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

#### gopiggy

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Hi

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

Excel 2010
ABCD
1
2
3
4
51111
62222
73333
84444
9
10
99
1001111
101
109
1102222
111
119
1203333
121
129
1304444
131
Sheet1
Cell Formulas
RangeFormula
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)))

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,

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.

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

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)

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?

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

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

What if the trouble is not noticed at all?

Replies
6
Views
295
Replies
3
Views
101
Replies
1
Views
245
Replies
4
Views
328
Replies
4
Views
441

1,203,751
Messages
6,057,147
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back