Fill Across in Single Cells the Contents of Merged Cells (without skipping!)

[Caelus]

Good day,

I'm trying to achieve what are effectively headers on one sheet according to what are effectively headers on another. The second sheet has merged cells (two merged into one) that each contain a person's name. I would like my first sheet to slave to this merged second sheet in a single-cell format. I have tried to fill across using the "=Sheet2!..." but of course this doesn't work because Excel doesn't pick up on the pattern. Is there a way for it to treat each merged set of cells as one cell so that it can fill across in single cells and remain dependant on the contents of the merged cells?

I did think at one point that I could use the INDIRECT function to piece together the pattern I was looking for in filling across but I couldn't come up with a solution.

Thanks for the help.

 

[RonB1111]

Assuming your merged cells are in the 1st row of Sheet2, enter this formula in A1 of the 1st sheet and copy across:

=INDIRECT("Sheet2!"&ADDRESS(1,COLUMN()*2-1))

 

[Caelus]

Thank you, that worked!

 

[RonB1111]

You're welcome!