Forum Discussion
Sorting data across linked sheets
Hello! I am looking for advice, I am hoping someone can help. My spreadsheet is created to be used so all the data entered into the main sheet is then pulled onto a second sheet via index and vlookup formulas.
=IFERROR(INDEX('Main Guest List'!$B$5:$B$158,SMALL(IF('Main Guest List'!$M$5:$M$158>$N$5, ROW('Main Guest List'!$B$5:$B$158)),ROW('Main Guest List'!1:1))-4,1),"")
=IFERROR(VLOOKUP($A4,('Main Guest List'!$B$5:$P$158),12,0),"")
All of the content on the second sheet auto populates using the above formulas, except for the single column that requires manual entry, Table Number - so once all the data has been entered in the main sheet, and the attending guests and their details are pulled over to the second sheet, people can go into the second sheet and just fill in the table number column.
The challenge is, if/when the data in the main sheet is sorted, all of the linked data in the second sheet also sorts accordingly (which is ideally how I want the sheets to function) - the issue I'm having is the data in the table number column that has been manually entered, does not sort in conjunction with each respective row. I understand why this row is not sorting, but I do not know how to rectify. I am wondering if there is even a way to do this or perhaps it's not possible?
I thank you very much in advance for any help anyone may offer!
Thank you!
Anne
Before we even try to resolve your issue, you must realize that the names in your images of the spreadsheet are names that are recognizable. One of the ground rules of this board is that we don't reveal database contents that should be kept private. I do realize that maybe you're using those names to disguise the real names of other anonymous people, but my recommendation would be that you use movie character's names rather than move star's names. Han Solo, for example, we all know isn't real; Harrison Ford is.....use the former, but not the latter.
So please remove those images and come back and ask the question with different data samples.
My apologies for the oversight, thank you for letting me know, I have updated with all fake names.
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
- mathetes
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.
