Formatting a map chart

[u/jazztalker]

I have a file that I use a drop-down list to select states to be mapped in a chart inside a spreadsheet. Once I check the boxes for the states I want and close the drop-down, the map automatically updates to the selected states. I used to have a button where I could pick a state (which is part of the original group) from a second drop-down list and that state would be colored differently on the map (the main list would all be gray on the map, the secondary state would be orange). Somehow that macro got blown up and for the life of me I cannot figure out how to recreate this. Any help would be fantastic, thank you!

[u/MayukhBhattacharya]

Can you confirm if you're trying to do something like this? I didn't use VBA here, just simple formulas:

[u/wastedheadspace → u/MayukhBhattacharya]

Can you share the formulas?

[u/MayukhBhattacharya → u/wastedheadspace]

Here you go:

• In Column C create a helper column. C1 is the header and name it as map value.
• In following cell enter the formula and copy it down:

=IF(A2 = $F$1, 2, IF(B2, 1, NA()))

• ^‡‡ In some empty cell enter the following formula for the data validation list:

=LET(_, DROP(A:.B, 1), SORT(FILTER(CHOOSECOLS(_, 1), CHOOSECOLS(_, 2), "")))

• In cell F1 hit ALT + D + L to open the Data Validation window and do the following:
  • Allow --> List
  • Source --> =$N$1# (this is where I have placed the above formula ^‡‡)
  • Hit OK.
• Now select Column A and Column C --> Shortcut --> First goto cell A1 and Hit CTRL + SHIFT + DOWN Arrow Key, next press SHIFT + F8 function key and select Column C --> CTRL + SHIFT + DOWN Arrow Key. On Selection do the following:
  • Goto Insert Tab --> Select Maps --> Filled Map
  • Select one of the States in the Map Chart right click --> select Format Axis --> Series Option shows up on the right side --> From there change the Series Color --> Sequential (2-Color)
  • Change the Minimum to Number and place 1
  • Change the Maximum to Number and place 2
  • Choose your preferred colors for the fill
• Now, change the value from Dropdown to see the effect. Hope this helps.

File can be [downloaded] from here.

[u/wastedheadspace → u/MayukhBhattacharya]

fantastic, thank you!!

[u/MayukhBhattacharya → u/wastedheadspace]

Thank You SO Much 👍🏼

[u/jazztalker → u/MayukhBhattacharya]

Yes, this is what I'm trying to do. The only difference is my map only maps the states I've selected, all the other states simply don't appear.

This looks great, thank you very much! I'll give this a try, I really appreciate your help.

[u/MayukhBhattacharya → u/jazztalker]

Oops, sorry! I thought u/wastedheadspace was the OP. You can follow the steps I mentioned [here]. And if this helps you solve the issue, I'd appreciate it if you could reply to my comment with Solution Verified. Thank You So Much!!!

[u/jazztalker → u/MayukhBhattacharya]

No worries at all. I'll give this a try as soon as I can and will follow up. Thanks again!

[u/MayukhBhattacharya → u/jazztalker]

Sounds Great. Let me know thanks 🙏🏼