I was given a spreadsheet created by another employee that contains 3 tabs, a "backup data" tab, a "legend" and then a "4 States" tab of 4 tables of region with corresponding maps to show the specific states. The legend tab contains a column with each state, a column with a SUMIF formula that sums the amount of orders within a given state based on the backup data tab, a count column that counts the number of rows in the backup data for a given state, and then a column called region, where I manually set the region based on the geographic location of the state.
The 4 states tab contains 4 tables, in each table is a header that corresponds to the region that I have given them in the legend tab, columns for state, orders and count. At present, these are simple cut/paste from the state, order and count columns in the legend tab. They also have maps to show a visual of the states included in each region. The current state of the spreadsheet is that we highlight and move the 3 rows of data from one region to the next, in order to be able to see the maps update, for a visual view of what the breakdown looks like after a move.
I have been asked to update the formulas on the legend and 4 states tab, to make the region column in the legend tab, update the a state, order, and count data on the 4 states tab is moved from one region to another. I am having a very difficult time figuring out how to do this.
For example, if I highlighted the data for MO in the Northeast region on the 4 States tab, and dragged it over to the Northwest region, I would like for the Region column on the legend tab, for MO to change from Northeast to Northwest.
Can anyone provide some guidance on how to make this work?
*Note: I had to remove the maps so that the doc would be small enough to attach.
Bookmarks