Reference a cell that is 10 rows below the cell that is being referenced to in the row above

[gopiggy]

I have a dilemma and can't think of a simple solution, although I am sure there is one that I just can't see.

If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?

I have tried using INDIRECT and OFFSET formulas but cannot do it without an absolute reference to a fixed cell, which defeats the purpose, since I can go into C8 and manually change it to =C130, C9 to =C140 etc.

Currently using Excel 2007 at work and Mac Pro at home. Thought about R1C1 reference, but don’t even know how to change to that style on my Mac.

Will appreciate any thoughts?

Thanks

 

[perimidt]

Hi

This should work:
=INDIRECT("C" &100+ROW(A3)*10)

 

[jim may]

Excel 2010
	A	B	C	D
1
2
3
4
5			1111
6			2222
7			3333
8			4444
9
10
99
100			1111
101
109
110			2222
111
119
120			3333
121
129
130			4444
131
Sheet1

Cell Formulas
Range	Formula
C5	=INDIRECT("C" &100+((ROW()-5)*ROW($10:$10)))
C6	=INDIRECT("C" &100+((ROW()-5)*ROW($10:$10)))
C7	=INDIRECT("C" &100+((ROW()-5)*ROW($10:$10)))
C8	=INDIRECT("C" &100+((ROW()-5)*ROW($10:$10)))

 

[gopiggy]

Excellent. Thanks perimidt, such a quick response too. Very impressive! this has been racking my brains for quite a while. I changed the reference ROW(A3) to ROW(A1) and C6 referenced to C110 and so on. Cheers,

 

[gopiggy]

Thanks Jim May, worked perfectly from Cell C5 onwards. Brilliant solution. Another quick response. I wish I had used this forum weeks ago when I first tried to crack the solution. It would have saved me many hours of heartache.

 

[gopiggy]

Regarding: "If C5 refers to the value in cell C100, C6 refers to the value in C110 ten rows below. C7 refers to C120, ten rows below that and so on. Is there a formula that you use within excel (not a macro) that allows me to copy the C7 formula to C8 so that C8 will reference C130?"

The solutions above are great and really helped me. However, now I have another scenario. What if the data that is being referenced is now on a different tab? T
hank you again in advance.

 

[perimidt]

Hi

You just have to add the sheet-name and en exclamation mark before the cell-referense, like this:
=INDIRECT("SHEET2!C" &100+ROW(A3)*10)

 

[Aladin Akyurek]

Hi

You just have to add the sheet-name and en exclamation mark before the cell-referense, like this:
=INDIRECT("SHEET2!C" &100+ROW(A3)*10)

Does this still work if we want to insert a row or two in front of the row housing the above formula?

 

[perimidt]

You'll probably have to adjust the (A3)-part afterwards.

 

[Aladin Akyurek]

You'll probably have to adjust the (A3)-part afterwards.

What if the trouble is not noticed at all?