Ingenious formatting needed?

[Al]

To present a spreadsheet to my client I hide the columns that are not needed
on a case by case basis.

When I hide un-needed columns the formatting of my "group titles" -
categories of personnel, that sit in a cell that is a merged cell across the
entire group - often gets messed up....squashed too much, spills over onto
the next group title.

Example:
Category "Senior Project Management"
Columns: D - M

For project "cars" I only need columns E, G & H, so I wish to hide the
balance. The title "senior project management" (merged cell D5 - M5) will now
be too big to fit into the three columns, and will spill over, onto the next
category.

Is there any way that the formatting of "Senior Project Management" can be
automated to fit the width of the remaining columns after I have hidden the
ones that I do not need?

 

[ken]

categories of personnel, that sit in a cell that is a merged cell across the
entire group - often gets messed up....squashed too much, spills over onto
the next group title.

All I can think of is to not use a merged cell. Select the cells that
need to contain the group title and format them with "Center across
selection" for the alignment, and check "Wrap text".

After you hide your columns you may have to double click on the row
boundary in the row headers to get it to realign, but it worked here
when I did that. I find that merging cells often causes more trouble
than it's worth so if I can get away without it then I will.

Hope that helps,
Ken

 

[Al]

Hi Ken, yeah, I guess that'd work, but I was hoping to find a more automated
solution as this crops up frequently. I will revert to "centre across
selection" though.

Is there a means of changing font size if, or example, the quantity of
characters exceeds the max for the width of the selection? (I'm probably
trying to achieve the impossible here!?)

 

[Gord Dibben]

Shrink to fit under the format>cells>alignment>text control may work for
you.


Gord Dibben MS Excel MVP

 

[Al]

Yes, formatting the merged cells using "shrink to fit" automatically does
this when I hide the columns I don't want! Great...thanks!