Displaying a cell relative to the position to another cell

T

Tibbs

Hi all - quick brain bender for Monday!

I have a sheet that has weekly data in it. What I would like to do is
find a way to display the value of a cell that is 52 cells (weeks) to
the left of the furthest right cell with a value in it on a row. Or if
this can't be done, then just the value 52 cells to the left of a cell
in teh formula.

As ever, any help gratefully received.

Regards,

Chris
 
J

JLatham

=OFFSET(AC1,0,-(COLUMN(AC1)-51))
will return the value in the cell 52 columns to the left of AC1. The
formula would go into cell AC1 itself.

To check the logic try it in a couple of cells that you can easily see at
the same time, example:
in cell C1 put a 6. In G1 put the formula =OFFSET(G1,0,-(COLUMN(G1)-3)) and
you'll see the 6 show up in G1 also.
 
Top