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 Gender
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 theinfo is getting mixed up.
So, I am not sure what would be the bestsolution 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...)
The Existing Connections doesn't give me anything.
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...