Forum Discussion
Sorting data across linked sheets
Why is the table number entered in the second sheet in the first place? Is it not possible to enter it in the "Main Sheet"? One of the tricks to assure data integrity is to separate your Input from your Output; you are effectively mixing the two here. Or maybe do the sorting only with the second sheet.
What I'm hearing from your description is:
- Collect some information in "Main Sheet"
- Extract some information from "Main Sheet" to "Second Sheet"
- Add some data in "Second Sheet"
- Sort data in "Main Sheet"
- Be surprised that "Second Sheet" hasn't kept up with Sort.
If we were sitting down face-to-face here (meeting in person), I'd want to review the whole process of data collection and processing, in other words, because it seems from your description that the sequence of activities itself is the source of the difficulty, so rather than fixing it at the tail end, let's see if we can prevent it in the first place.
For example, would the following scenario make sense?
- Maintain all data in "Main Sheet"
- Create extraction(s) for various purposes such as:
- Table assignments
- Alphabetical list of attendees
- Alphabetical list of attendees by category
- Etc
Those "extractions" can take the form of various "dashboard" sheets or reports that are nothing but Outputs, created by means perhaps of the very same formulas (or similar ones) that you are already using. You also might find useful some new Dynamic Array functions described on this YouTube video (you will need the most current version of Excel).
https://www.youtube.com/watch?v=9I9DtFOVPIg
Thank you so much for your thoughtful insights. You are exactly right, if I stick to instructing people to only sort from sheet two there are no issues. The rationale behind inputting the table numbers on the second sheet is that the intended purpose of the second sheet is to only pull over data for guests who RSVP yes, so people can easily see a compiled list of only those guests, and then assign table numbers. I am sure you are right and there is a better way to accomplish this, but to answer your question, that was the motivation behind the current arrangement.
Alajandra I have the same problem.
for a number of reasons, it's not practical to have a single master dataset.
For context, I have a spreadsheet with patient names, and several columns of clinical data which are not relevant to my colleagues in the admin/ reporting department. 4 or 5 columns pull through from my sheet to theirs, and they have several additional columns in their sheet which are not relevant to mine.
When I sort/ filter my sheet, the columns from my sheet sort in theirs, but their own columns don't, jumbling their dataset. To clarify, I am in no way an excel expert and I need the simplest possible fix. Thanks!
