Displaying a cell relative to the position to another cell

[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 the formula.

As ever, any help gratefully received.

Regards,

Chris

[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.


"Tibbs" wrote:

>
> 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
>
>
> --
> Tibbs
> ------------------------------------------------------------------------
> Tibbs's Profile: http://www.excelforum.com/member.php...o&userid=15947
> View this thread: http://www.excelforum.com/showthread...hreadid=561980
>
>

[Tibbs]

Many thanks for that, it worked!

Chris