3

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
  • Is the data dynamic? In other words, are you adding, removing, or changing the master data regularly? – Excellll Dec 1 '15 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. – Clare Macrae Dec 3 '15 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 '18 at 16:25
3

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.

  • 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 '15 at 13:45
  • Sorry, I didn't get good sleep and the coffee machine was busted today :) Will fix! – DraxDomax Dec 1 '15 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.

  • 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? – Clare Macrae Dec 1 '15 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 '15 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.