How to best manage what should be one table, but across several worksheets?

Copper Contributor

Hi all!

The member registry of our NGO is extensive. We are doing segmentation and collecting information about each of our member associations on several themes. One Excel sheet with one table would therefore be too massive to be usable on a daily basis. 

In the past, we have solved the issue by having the list of member numbers and names on Sheet1, and then having on Sheet2 A1=Sheet1!A1 and so on. However, we did not have it as a table, but now we would like to segment/view/reorder the list based on the information.

The issue is that, if we sort the table on one sheet, the cell references in the next sheets will (as far as I can tell) shift, be they absolute references or relative table references. Here's an example:

Sheet1 table columns:
Member number
Member name
Age category

Sheet2 table columns:
Member number
Member name
Main language preference
English proficiency
German proficiency

If I sort the Sheet1 table by name instead of number, the order of all the information in the table will change. However, in the Sheet2 table, the order of the two first columns will change, but not the order of the third one or the ones after that. So the info is getting mixed up.


So, I am not sure what would be the best solution to that, or if either would be a solution:

- Option 1: Making one massive table on one sheet with the member number and name once in the first two columns 
=> Would there be a way to somehow "tag" each thematic section (which otherwise would be in a separate sheet) for easy browsing, so you don't need to scroll through 541 individual columns about f.ex. age and gender before getting to the language information you're looking for?

- Option 2: Splitting the contents of the table across several sheets 
=> Is there a way to make all the information across sheets behave like one single table, i.e. sorting the table on one sheet would sort the data in all the other sheets at the same time?

I'd be grateful for answers to the questions above, and for any other suggestion which could make this massive amount of data better manageable. Extra points for solutions that do not require anything else than Excel and which my coworkers would not break in two minutes (they still manage to break formulas in protected sheets, so the threshold is pretty high...)


3 Replies

Why don't you make a view of the data?


1. Highlight Data

2. Insert Table

3. Data > Existing Connections > Tables

4. Double Click on your table

5. Decide where to put the copy of the data (New Sheet Most Likely)

6. Sort the table the way you want

7. When additional data is added to the original source table.  You can just go to Data > Refresh All and the table you have arranged the way you like will update accordingly.


If you don't need to sort but only need to filter or hide/unhide rows you can use the Custom View Option.


View > Custom View

Hi Matt!

The Existing Connections doesn't give me anything.

existing connections.jpg

I dug around a little more, and I'm leaning more and more towards a single massive table, be it only for ease of use (can't expect our interns f.ex. to know how to use pivot tables or other arcane Excel things). 

You will only see a Table section on the Existing Connections if you have a table object in your workbook.  Once this is set up you would only need to teach interns to click the Refresh Button.  Even better you can set the data to refresh on open.  Then they never need to do anything and it manages itself...


Step 1:




Step 2:


Step 3: