Referencing every Nth Row from another worksheet

[kmullens]

Hi,
I have data in another worksheet in a fixed column, however I want to reference every 55th row onto another worksheet. I have done 4 enters, however when I drag across it reverts to the first entry not the next 55th. There are heaps so I'd rather not manual do. Any help would be appreciated.

Thankyou

[Tony Valko]

Are you copying the formula down a column or across a row? What is the cell address of the first cell that will hold the formula?

[kmullens]

Hi Tony,

My data from the original sheet in down the column, I'm transferring to new sheet across a row.

='PC1'!$M4
='PC1'!$M59
='PC1'!$M114
etc

[kmullens]

I also want to do a more complicated formula later.

=('PC1'!$K29+'PC1'!$K39+'PC1'!$K49)/('PC1'!$L29+'PC1'!$L39+'PC1'!$L49)
=('PC1'!$K84+'PC1'!$K94+'PC1'!$K104)/('PC1'!$L84+'PC1'!$L94+'PC1'!$L104)
etc

But I thought I'd start with the basics 1st lol.

Thanks

[Tony Valko]

Let's assume you enter the formula in cell A1.

=INDEX(PC1!$M4:$M1000,COLUMNS($A1:A1)*55-54)

Copy across as needed. Adjust for the correct end of range where I use down to row 1000.

[gak67]

Please Login or Register  to view this content.

[kmullens]

Thank-you you're amazing!

[Tony Valko]

Using the COLUMN/ROW functions leaves the formula vulnerable to new column/row insertions before the cell that contains the formula.

Using the COLUMNS/ROWS functions are more robust.

If the formula is entered in cell A1:

COLUMN() = 1

COLUMNS($A1:A1) = 1

If you were to insert a new column A:

COLUMN() = 2

COLUMNS($B1:B1) = 1

[Tony Valko]

Thank-you you're amazing!

Can't tell who you're replying to but in any case, we appreciate the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.