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
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 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...)
Thanks!
