Running pivot table scenarios without duplicating the whole table

urazack

New Member
Joined
Jan 21, 2019
Messages
4
Hello, This is my first post on MrExcel.

I've learnt how powerful tables and pivot tables are for generating views/reports of tabular data. As a result, I've taken production supply data at my company and put it into a table and have created views/reports that others can just refresh each time data is added to the bottom of the table.

The one challenge I now have is that sometimes my team is asked for a report IF a specific production line has slightly different numbers. Currently, to do this, I would duplicate the entire table below itself and, in a new "Scenarios" column, add text like "Scenario 2" in each row. (I'd add "Scenario 1" next to each row in the "Scenarios" column of the original table.) I'd then have to simply add the "Scenarios" column to the "Filter" box in the pivot table options and use this to choose report on the specific scenario.

The challenge I have with this approach is that the original table can be quite large e.g. 10,000 rows and the changes may only affect 50-100 rows. We may also be asked to run several such scenarios. Hence it seems inefficient to have to duplicate 10,000 rows multiple times for each scenario.

Is there a more effective approach where one can keep the existing table and perhaps only add rows for the changes, and have the changes be pulled into the respective scenarios in the pivot table views/reports?

Thanks for the help!

Kind regards,
Uwais
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
When you create a pivot table you have the option to place it on a new worksheet tab, doing this multiple times reuses the existing table without creating a copy for each new pivot table. The worksheet tab can be described such to highlight the uniqueness of the pivot table on that tab. You can copy a pivot table to a new tab and then just tweak the PT to show the new option.
 
Upvote 0
Thanks for your response tcardwell. I think I may not have explained my challenge well enough.

I recognise that I can use the same table to generate multiple pivot tables, each configured to present a specific report/view. However, what I can't figure out is how to manage the amount of data you include in the original table, so that it allows you to run different scenarios in the pivot tables. Running a scenario (pivot table view) assumes you have all the data for that scenario in the table. If you decide to tweak specific, limited categories to create another scenario, I currently have to duplicate all the other rows that weren't tweaked as well and include them in the new scenario. I'm looking for a way to not have to duplicate the rows that aren't tweaked.

I've tried to illustrate the challenge in the sample spreadsheet at the following link :
https://drive.google.com/open?id=1jSu8Lg032etYgRkQ9X9hoLr_ZcGaZgqa

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top