Match offset or lookup

[spartacus1]

Hi

I have created a 12 column spreadsheet with January to December headings on row1. Under these on row 2 are numbers in each cell.

In a row 3 cell, what formula should I use to take data from any row 2 cell in columns to the left of that particular cell - e.g. I want to enter under December on row 3, data say from October row2, ie 2 months back but make this a formula which I could easily change to take data from row 2 say 4/5/6months back etc.

Thought It might be an offset formula but can't get it to work
Any ideas would be very welcome.

[NBVC]

Did you write your offset function something like this?

=OFFSET(L2,0,-2)

where L2 is the number under December... and you are entering the formula in L3

A less volatile way could be:

=INDEX($A$2:$L$2,COLUMN()-2)

where A2:L2 contain the numbers for Jan to Dec and again formula is in L3.

[spartacus1]

excellent, that works - thanks!

[spartacus1]

one further question I have just thought of...

where you have ' -2 ' in your formula after the word 'column', could this be replaced somehow with a cell reference (so you could have the '-2' on a different sheet such as a model assumptions page?

[NBVC]

Yes you can replace it with a cell reference from another page..