Pivot table ideas for dynamic changes

[Brand04]

Hello! I have a spreadsheet that breaks down accounts by region (west,east, south) and each region has info for the number of accounts, spending per year, etc. We want to analyze the data to make sure the states being split into the regions is fair. We want to dynamically play with the data and move states to different regions in real time to see how the comparison evens out. For instance, if the accounts in the east region is a little low we could move florida into the east dynamically and see if it evens out against the other regions. Any ideas on how to set it up? I am pretty good at pivot tables, but i can’t visualize how i can set this up. Help please

 

[AlanSidman]

Create your PT as you normally would. Make your changes in your source data (change regions for a state). Click on Refresh and the PT will update.

 

[Brand04]

Hello! I have a spreadsheet that breaks down accounts by region (west,east, south) and each region has info for the number of accounts, spending per year, etc. We want to analyze the data to make sure the states being split into the regions is fair. We want to dynamically play with the data and move states to different regions in real time to see how the comparison evens out. For instance, if the accounts in the east region is a little low we could move florida into the east dynamically and see if it evens out against the other regions. Any ideas on how to set it up? I am pretty good at pivot tables, but i can’t visualize how i can set this up. Help please

Create your PT as you normally would. Make your changes in your source data (change regions for a state). Click on Refresh and the PT will update.

Could you create 2 drop down lists (one being states and one region) and move “Florida” to “East” and create a macro? I guess i was hoping to change with a few clicks vs manually changing the source file. Open to ideas.

 

[Chihiro]

I'd recommend uploading sample workbook. Demonstrating your need and desired output.