4

I have an Excel spreadsheet with around 10 columns and 100 rows...

Depending on context, I want to see the data in different orders, e.g.

  • Scenario A
    • Sort spreadsheet by Column 1
  • Scenario B
    • Sort spreadsheet by Column 3, 6, 2 (descending)
  • Scenario C
    • Sort spreadsheet by Column 1, 2, 4, 3

etc...

It's driving me nuts keeping changing the sort order...

Is there a way to create additional Sheets that each show the data from the first sheet, just sorted in different orders

Then I could have sheets called e.g.

  • Master Data
  • Scenario A
  • Scenario B
  • Scenario C
3
  • Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly?
    – Excellll
    Dec 1, 2015 at 20:14
  • 1
    Yes, the data is dynamic, changing regularly, and I was hoping that the views would update their content and sort order automatically, or at least with as few steps as possible. Dec 3, 2015 at 8:08
  • It is frustrating, but Excel does not seem to support this functionality, at least not without writing a script.
    – Sam Buss
    Jun 24, 2018 at 16:25

2 Answers 2

4

Yes you can! It's hacky but easy to do:

1. Open a ScenarioA sheet. Do a "formula copy" of the whole sheet. For example:
Put "=MasterData!A1" in cell A1 of ScenarioA sheet and drag that across.
Now, you have a copy of the MasterData but that's not robust for moving around!

2. Find/Replace: =MasterData! with =MasterData!$

3. Find/Replace: =MasterData!$A with =MasterData!$A$ (do this for all columns)

Now, the references in the scenario sheet are fixed and if you sort, they will not re-evaluate to something else.

2
  • 1
    Doesn't step 2 break the process? If you search for =MasterData!A after you've already added the first $ you won't find anything.
    – Kyle
    Dec 1, 2015 at 13:45
  • Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix!
    – DraxDomax
    Dec 1, 2015 at 13:50
0

You can use data connections. First, format your master data as a table by clicking anywhere inside the data and pressing ctrl+T. Then on each of your three sheets go to the Data Tab >> Get External Data Group >> Existing Connections >> Tables tab and select your table.

When you add or change your data you must refresh your connections by going to the Data Tab >> Connections group >> Refresh All.

2
  • Thanks. I've done the Ctrl+T bit, and created the table. When I got to Data Tab >> Get External Data Group >> Existing Connections, there is no Tables tab. When I click on 'Show:' dropdown, and choose 'Connections in this Workbook' it says '<No connections found>'. Any ideas, please? Dec 1, 2015 at 15:23
  • @ClareMacrae From the Existing Connections dialog, click on Browse for More... then navigate to where your workbook is saved and select it. Then you can select the Master Data worksheet.
    – Kyle
    Dec 1, 2015 at 15:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .