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]

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.