ExcelBanter - View Single Post

Dallman Ross

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)

-dman-