Copying every 5th column cell into a list

[khammonds294]

Hi

I have a multiple sheet workbook - one of the worksheets has a series of event headers that run across 5 merged cells ("event1"= D2:H2, "event2"= I2:M2, "event3"= N2:R2 etc) I have a summary sheet that pulls statistics from the entire workbook and I would like to have a summary of the event names.

So I would the event headers on sheet 1 pulled through to create a list on Sheet 2 (A3, A4, A5, A6 etc)

Sheet 1
"event1"= D2:H2, "event2"= I2:M2, "event3"= N2:R2

I would like this copied to Sheet 2
A3 = "event1"
A4 = "event2"
A5 = "event3"

Is this possible - can anyone help? Thanks!

 

[Special-K99]

I dont know why people moan about merged cells. if the cells are linear, occupying the same range length (which they usually are) then a formula can be written to return the correct cell. Like this...

in A3
=INDEX(D$2:ZZ$2,1,(ROW()-3)*5+4)
copy down the column

This works because your events are evenly spaced out every 5 columns (same range length as mentioned above) beginning at column 4

Adjust the ZZ reference for wherever your data ends.

 

[khammonds294]

Thanks - that's good to know about merged cells, however the formula is returning a value of zero instead of the "event name text" - any ideas how to return the cell text as opposed to a number?

Thanks!

 

[Special-K99]

You'll have to adjust +4 in the formula.
I'm assuming column D contains "event1" , if not change it from 4 to 5, 6, 7, 8 until it displays "event1"

If this doesn't work can you let me know:

What single cell contains "event1" ?
What single cell contains "event2" ?

 

[khammonds294]

You'll have to adjust +4 in the formula.
I'm assuming column D contains "event1" , if not change it from 4 to 5, 6, 7, 8 until it displays "event1"

If this doesn't work can you let me know:

What single cell contains "event1" ?
What single cell contains "event2" ?

Perfect - Changed +4 to +1 and it worked perfectly, thanks a million!

I was getting confused with the final part of the formula as I didn't understand the Row()-3*5+1

but regardless it is working now so thank you!

 

[Special-K99]

Oops! yeah my bad, I was thinking column 4 9 14 19 etc
but we indexing from column D so we want minimum of 1. So "events" are in columns 1 6 11 16 etc

Your events are in columns 4 9 14 19 etc
so every 5 columns starting at column 4
But as we are indexing from column D we want columns 1 (D) 6 (I) 11 (N) etc
And we are using rows 3 4 5 for these values
So 3 goes to 1, 4 goes to 6, 5 goes to 11.

(11-1) / (5 - 3) = 5

so to get 1 for row 3
subtract 3 to get 0
multiply by 5
add 1 = column 1

for row 5
subtract 3 to get 2
multiply by 5 = 10
add 1 = column 11
so formula is

(row()-3)*5 + 1

So each row we have a factor of 5
so subtract

 

[Special-K99]

Actually it would have been better to do thisbut it amounts to the same thing.

=INDEX(D$2:ZZ$2,1,ROW()*5-14)

given row 3 4 5 we want columns 1 6 11 etc

y=mx+c basic maths linear equation

(11-1) / (5-3) = 5

m = 5
Column number = 5 * row number + offset c
c works out to be -14

so column number = 5 * row number - 14