I have three main sheets in my spreadsheet. The first is a collection if items with relevant details for them, the second is a list of people. The third is used to set some basic relations between the two.
Here are some short examples of the three sheets:
|Item #1||This is an example||Loved: Bob. Liked: Charlie|
|Item #2||Another example||Liked: Alice, Charlie|
The third column here is data from sheet 3.
(The formatting of the table for sheet 2 only works in the preview for some reason, it's just a list of names starting on the first row.
Because sheet 3 is using data from the first two sheets, it looks like this behind the scenes:
My problem is that both sheet 1 and sheet 2 are alphabetised which changes the order of things for sheet 3 if a new item or person is added. The reactions in the third sheet need to match up with both the item and person no matter what.
I unfortunately learnt all this the hard way when I added new items and people and I wasn't prepared for all the data being shifted around.
Is there anyway I can keep the data in sheet 3 bound to the respective items/people? Before anyone suggests it, no, I will not be removing the alphabetisation of sheets 1 and 2.
I've created an example spreadsheet for all of this. Feel free to make a copy to play around with this as I'd love to be able to actually see a working result.