An excel cell need to be fixed, however the fixed cell should change, when i copy and the paste rows below

Question

I have linked cells P11 to P17 with the cell E9 and formula for that I used is highlighted in image.

If I want make another control type, I will simply copy rows (8to17) and paste it in rows (19to28),

so in this case, do we have any formula, such a way that, the Type 1B should automatically referred and displayed in cells P22 to P27

Answer 1

This formula would go in P11 and it assumes that the copy area is always 11 offset by 11 rows as your example suggests.

=INDEX(E:E,INT((ROW($ZZ1)-1)/11)*11+9)