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

 

[gopiggy]

Per Erik,
Perfect. Seems obvious now, thank you again, this has helped me immensely. Don't know what I would have done without both of your replies.
Cheers!

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)