Running pivot table scenarios without duplicating the whole table

[urazack]

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

 

[tcardwell]

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.

 

[urazack]

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.