Dynamic Formula Creation??
 

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.

Dynamic Formula Creation??
 

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-

Dynamic Formula Creation??
 

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)