Offset from refernce in other cell

J

Jonathan Blitz

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
 
B

Bob Phillips

Without a clear order you have no chance.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Jonathan Blitz said:
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


David McRitchie said:
A1: =OFFSET(G1,ROW()*10-1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
Click to expand...
 
J

Jonathan Blitz

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


David McRitchie said:
A1: =OFFSET(G1,ROW()*10-1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
 
R

RagDyer

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!
==============================================


Jonathan Blitz said:
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


David McRitchie said:
A1: =OFFSET(G1,ROW()*10-1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
Click to expand...
 
M

Murtaza

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.

David McRitchie said:
A1: =OFFSET(G1,ROW()*10-1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
 
D

David McRitchie

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,)

Murtaza said:
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.

David McRitchie said:
A1: =OFFSET(G1,ROW()*10-1)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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
Click to expand...
Click to expand...
Click to expand...
 
Top