Offset from refernce in other cell

  • Thread starter Thread starter David McRitchie
  • Start date Start date
I need to point a cell to another as an offset from a reference.

For example,
cell A1 formula is =G10

I want

cell A2 formula to be =G20

I need this to be dynamic as I need to drag it along the whole spreadsheet.

I can't seem to get this to work using the offset or indirect functions.

Any ideas?

Jonathan Blitz
AnyKey Limited
Israel
 
Without a clear order you have no chance.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I don't quite understand what you wrote.

I'll give a more concrete and wider example.

Cell B7 is =Sheet2!D8 Cell C7 is =Sheet2!D18 Cell D7 is
=Sheet2!D9 (there is no clear order here)

I need :

Cell B8 is =Sheet2!O18 Cell C8 is =Sheet2!O18 Cell D8 is =Sheet2!O9

Jonathan
 
I'm assuming you have a typo, and B8 should reference Sheet2!O8.

Also assume that your more interested in a pattern to reference cells across
columns, every 11 columns, as you copy down.

If that's the case, copy these 3 formulas into
B7, C7, and D7, and then copy down to reference out along Rows 8, 18, and 9,
as far as needed:

In B7:
=INDEX(Sheet2!$8:$8,ROW(A1)*10+ROW(A1)-7)

In C7:
=INDEX(Sheet2!$18:$18,ROW(A1)*10+ROW(A1)-7)

In D7:
=INDEX(Sheet2!$9:$9,ROW(A1)*10+ROW(A1)-7)


--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Correction with reference to context

Wrong: A1: =OFFSET(G1,ROW()*10-1)
Correct: A1: =OFFSET($G$1,ROW()*10-1,,1)

Just for information & in case if someother might want to use this.
 
Hi Murtaza,
Thanks for the correction the following would also have worked,
the first one perhaps easier to recognize.
=OFFSET($G$1,ROW()*10-1,0)
=OFFSET($G$1,ROW()*10-1,)
 
Back
Top