Map Alerts

[Former Member]

I have a continental US map. On my spreadsheet, each state is followed by a value which is displayed when you hover over the state. No problem there.

I have a complicated calculation that I do in the spreadsheet for each state. I have the calc result in assigning either a 0, 1, 2, or 3 to each state. I want to alert white on 0, green on 1, yellow on 2, and red on 3. But I can't figure out how to have the alerts reference those data points.

Here's how it's laid out:

Row 1, Columns 1 - 52 are the state names

Row 2, Columns 1-52 are the values I want displayed (this is working fine)

Row 1, Columns 53 - 105 are the state names again

Row 2, Columns 53 - 105 are the values 0, 1, 2, or 3; if 0 then I want the state to appear white, if 1 then I want the state to appear green, if 2 then I want the state to appear yellow, if 3 then I want the state to appear red.

Is this possible?

Thanks in advance!

[Former Member · Accepted solution]

HI Michael

The trick here is to place one map directly on top of the other. The map at the back uses your alert formula with Mouse Over switched off, and the map in front has your actual values for the Mouse Overs, but make this one fully transparent.

Job done

[Former Member · Accepted solution]

Charles --- Okay, I've been struggling with this for the better part of 3 days now. I just used your workaround and it worked beautifully! I can't thank you enough!!! U da man!!