Data in multiple cells within a merged cell is possible!

[hippielatenight]

Hi,

Actually, I don't have a question, so I'm not exactly sure where to post this. I have no formal experience with excel, everything I know is self-taught. Love your site!

I have often seen it mentioned that data cannot be stored in separate parts of a merged cell. This is not true. It *is* possible to store data in every cell that has been merged. I haven't explored it to its full extent yet, but this is what I have found:

Create some merged cells of the desired size, but in a *different* location than desired. For example, I want merge cells d3:f5. So I create a 3x3 merged cell somewhere else, say g24:i26. The location doesn't matter.

Next, open the control toolbox and create a checkbox (just an example, other controls work). Link the control to a specific cell within your desired area (say cell E4, for example), as long as it's not cell D3!!

When you're finished creating the link with the control box, exit design mode.

Then, copy/paste your merged cells into proper location overtop the linked cell. The linked data from the control box (text, boolean) remains in cell e4 and is not affected when the data in cell d3 is changed. The data in cell E4 can be accessed normally from elsewhere (and continues to be linked to the control box). You can delete the control box, and the data will remain. I haven't found a way to use formulas, though, just data. Yet.

This is perhaps a roundabout way of doing things, but it works.

Hope this is of some interest, and I hope it can help someone!

I'm using Excel 2003 Student/Teacher Edition, BTW.

- Hippielatenight

 

[DJ Spreadsheet]

I also discovered something with merged cells today.
By using the format painter to merge cells, you can also hide values. I suppose its an Excel feature to ensure you don't loose data when using the format painter (because it doesn't warn you with an error message like regular merge).

I would love to put data behind there, besides just a true/false from a checkbox. I tried everything I could think of through VB, but anything I try just blanks out the cell.

I actually want to put the value of the top left cell. This way it would be in all of the cells, making it feasible to pivot the data.

 

[DJ Spreadsheet]

I figured out how to fill in data in a document with multiple merged cells (where the cells are only merged on a column)

1. Copy to a new sheet
2. Unmerge all

3. use this method to fill in the blank cells after unmerging:

http://www.mrexcel.com/forum/showthr...t=merged+cells

4. select all on the original sheet and use format painter to the destination sheet, now all cells will be merged as they were on the original, and if you unmerge, data will be visible in each cell.