Dynamic Formula Creation??

[mdailey]

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' having Feb
dated & his being in row five. I have tried playing around using
OFFSET, but haven't gotten it to return what I'm expecting it to.
Please share any suggestions that you may have.....THANKS!!! M.

 

[Dallman Ross]

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-

 

[Dallman Ross]

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)