ref cell in another worksheet

[brett316]

Could anyone let me know how to do a cell reference and then add a number of rows and reference it again please?

so e.g.

='Worksheet'!F20 is the first row

then I need to reference F79, so 59 rows down and then another 59 rows down etc etc. There is a big list so what is the easiest way to refernce this? Can you do something like F20+59 reference, F79+59 reference and so on?

Hope that makes sense

Cheers.

Brett

 

[Jared_8878]

here is how to do it for a sheet:

=INDIRECT("Sheet3!"&ADDRESS(5,5))

that will reference sheet3's cell of E5

then you can add in stuff in address such as
row()+10 or whatever you need to get the adjustments as you copy down.

 

[p45cal]

check out OFFSET

 

[brett316]

Thanks OFFSET works well but then how do I get the row that is being referenced in the forumla to then be the starting cell for the next?

So the first references F20, then the second is 59 rows down i.e. F79, then how do I make the next formula start with F79 and then add on another 59? And so on and so on. Because I will need to copy the formula all the way down and at the moment the next fornula just adds 1 onto the reference cell.

 

[brett316]

bump

 

[p45cal]

Say you have a formula in row 4 somewhere referring to F20, and you want the formula in the row below to refer to F79, you could use row() which returns the row number of the formula that the cell is in and some arithmetic giving he likes of:
=OFFSET($F$20,(ROW()-4)*59,)
starting in row 4 and copied down.

 

[brett316]

Great stuff thanks