Formula to get date from merged cell to other field

Dear all,

thanks for last help.

i have sheet 1.. some cells are merged for 4 lines. i want data of this merged cell to sheet 2 by a formula.

attached is my requirement.. can you help please?

i have put formula in sheet "my requirement".. but this formula will not work if i copy and past down. any solution please?

thanks.

c2 copied down:

=INDEX(data!$E$2:$E$20,1+4*(ROWS(C$2:C2)-1))

Put this formula in C2 and copy down:

Formula:

Please Login or Register  to view this content.

PSA: Merged cells are a very bad practice in almost every situation. Don't use them.

Values as displayed

	C
1
2	abcd
3	xyz
my requirements

Underlying formulas

	C
1
2	=INDEX(data!E:E,(ROW()-2)*4+2)
3	=INDEX(data!E:E,(ROW()-2)*4+2)

Did you change the 20 to be the same as the last row of your data?

Showvus what you mean by "did not work properly".