Excel reference every nth row 3

[agp4]

I have two worksheets in the same workbook. In worksheet one I have a number of records that populate about 9 rows each with each record representing one week. I have copied and pasted the original record (9 rows) to display a whole year. In worksheet two I want to be able to reference the last row of every record (every 9th row). I then want to be able to copy this reference in worksheet two for every week. The problem I have is that when I copy and paste it does not reference the 9th row of each record in worksheet one, but instead increments by one row! Could anyone please suggest a solution? Apologies if this is not very clear but I am sure this must be fairly simple! Many thanks.

 

[mike1955]

The only way I can see of doing this is with a macro - here you would build the copy row in a loop constructing the copy cell reference as part of the loop counter

 

[AnotherHiggins]

In A1 on sheet2, type
[COLOR=blue white]=indirect("sheet1!A"&9*row())[/color]

If you need to start on the second row, you could just change it to
[COLOR=blue white]=indirect("sheet1!A"&9*(row()-1))[/color]

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[rvnguy]

agp4,

Try looking at the thread referenced. This appearsto be similar:

thread68-1163545


rvnguy
"I know everything..I just can't remember it all

 

[agp4]

Thank you - the solution by anotherhiggins worked a treat!!!

 

[AnotherHiggins]

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[macropod]

Similarly:
=OFFSET(Sheet1!A$1,(row()-1)*9,)

This is a bit easier to use than the INDIRECT function, since you don't have to manually update column references if you want data from multiple contiguous columns. Likewise, the function will still work if you change the source sheet's name - the INDIRECT function won't work until you manually change the sheet references.

If you want to start from, say, B10, change A$1 to B$10.

Cheers

 

[dcompto]

I would like to add my thanks and a star to both anotherhiggins and macropod.

 

[dcompto]

For any newbies that may try macropod's formula using agp4's scenario above, change the formula to

=OFFSET(Sheet1!A$9,(ROW()-1)*9,)

and just copy it to all desired cells comprising the number of contiguous columns needed.