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

#### Caelus

##### New Member
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.

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Assuming your merged cells are in the 1st row of Sheet2, enter this formula in A1 of the 1st sheet and copy across:
Code:
``=INDIRECT("Sheet2!"&ADDRESS(1,COLUMN()*2-1))``

Thank you, that worked!

You're welcome!

Replies
3
Views
135
Replies
0
Views
169
Replies
6
Views
387
Replies
4
Views
74
Replies
2
Views
116

1,203,354
Messages
6,054,919
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back