Dragging Formula down through merged cells

[twmills]

In Sheet1 I want to drag a simple cell reference formula (=Sheet2!B4) down through some merged cells. But when I do that the formula changes by the number of rows that make up the merged cells. What I need is the reference only increase by one 1....rather than the number of rows the the merged cell is.

On the attached example is the final outcome of what it should look like on Sheet1. I need a formula in column G to refence the list of Dates on Sheet2, in order as they're listed. So I need G4 in Sheet1 to pull what's in B4 on Sheet2 (1/5/2021), then when I drag the formula down on Sheet1 to the next merged cell, G8 will equal the next date on the list in column B in Sheet2 (which is cell B5 - 1/7/2021).

Hope that makes sense.

Thank you!

[Paul_Hossler]

Well, most people here will tell you how evil merged cells are

Like anything else, they have their uses (usually)

If you have to use them, then the easiest way I can think of is to use a helper column

Capture.JPG

Maybe someone else will have a better idea

[p45cal]

In merged cells which include cell G4:
=INDIRECT("Sheet2!B" & INT(ROW()/4)+3)
copy down.

Or likewise with:
=OFFSET(Sheet2!$B$3,ROW()/4,0)

or:
=INDEX(Sheet2!$B$4:$B$8,ROW()/4)

[twmills]

Perfect thanks everyone!

[twmills]

In merged cells which include cell G4:
=INDIRECT("Sheet2!B" & INT(ROW()/4)+3)
copy down.

Or likewise with:
=OFFSET(Sheet2!$B$3,ROW()/4,0)

or:
=INDEX(Sheet2!$B$4:$B$8,ROW()/4)

yes. I liked the Index one.

That was perfect.

Thanks so much!