Heat Map

[Desoto]

I am trying to develop a "heat map" using Excel Conditional formating. I
don't have a problem creating a basic conditional formatting table (heat
map), however, I want to add two additional features. I want to merge cells
so the the size of the merged cells is in relation to the signficance of the
cost element or plant (more dollars = larger cell). I have not had a problem
with that feature, however, in addition to the % positive or negative the
cost element is to budget I also want to display a three letter acronym
representing the plant. For example if the Atlanta plant was 3.4% favorable
on labor cost the cell would be green and the text in cell would reflect ATL
+ 3.4%. I need the plant label so the reader will know what plant was
favorable the 3.4%. I thought I could build the heat map using the
conditional formatting and then build the label in the cells using
concatenate or copy paste special from another worksheet but neither approach
worked out.
Any thoughts on how to design would be greatly appreciated!!

 

[thexlguy]

I'm having a little trouble figuring out what your spreadsheet look
like in my head , but I'll give it a go... I think a formula t
create the label is the best approach.

I envisioned a second table with the percentages loaded into the cells
Then, I would use a formula something like this:

=[ATL_label_cell] & IF([percent_cell]>=0, " + ", " - ") &
TEXT([percent_cell],"0.0%")

where [ATL_label_cell] is the cell that contains your city labels
and [percent_cell] is the cell that contains the percentage you want.

So, if the ATL label was in B1, and the percentage for ATL was in F1:

=B$1 & IF(F2>=0, " + ", " - ") & TEXT( F2,"0.0%")

Hope this helps!
Mike

'TheXLGuy.com Main Page' (http://thexlguy.com)