About Us

Dallman Ross

In , Dallman Ross <dman@localhost.
spake thusly:

In .com,
spake thusly:

I am trying to a create a dynamic formula which can reference the
right-most column in a particular row which has data in it. For
simplicity sake let's say I have a list of salemen in column A, &
then in cells B1-M1, I have the months of the year. After each
month ends, new sales data is populated in the next column (the
'right-most'), & that process continues each month. On another
sheet, I have a cell which I want to show "last month" sales &
"last month-1" sales. Obviously as each month is completed,
"last month" moves from column b, to c, to d, .... So if I want
to reference John Smith's last month sales volume & he is in row
5 & the current month is March, then the cell & want to reference
to get that value is "C5"...w/ 'C'

If the data are numbers, then this should work to reference Row 5
for Salesman Smith:

=OFFSET(A5,0,MATCH(-99999999,5:5,-1)-1)

Or easier still, and the cells don't need to be limited to numbers:

=OFFSET(A5,0,COUNTA(5:5)-1)

-dman- (thought of that while lying in bed with the computer off)

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Find value in array	Brook6	Excel Worksheet Functions	26	January 30th 07 09:40 PM
Is it possible?	DakotaNJ	Excel Worksheet Functions	25	September 18th 06 09:30 PM
Need help with a dynamic lookup and logical formula	rwwkbw	Excel Worksheet Functions	2	March 31st 06 01:57 PM
Help with dynamic sum formula	Aussie CPA	Excel Worksheet Functions	4	October 7th 05 05:28 PM
Formula checking multiple worksheets	sonic-the-mouse	Excel Worksheet Functions	2	June 5th 05 07:48 PM

Menu

About Us