Keeping row content bound in Google Sheets

Question

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:

Sheet 1

Name	Description	Reactions
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.

Sheet 2

• Alice
• Bob
• Charlie
• David

(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.

Sheet 3

	Alice	Bob	Charlie	David
Item #1		Loved	Liked
Item #2	Liked		Liked

Because sheet 3 is using data from the first two sheets, it looks like this behind the scenes:

	=TRANSPOSE('Sheet 2'!A1:A)
=ARRAYFORMULA('Sheet 1'!A2:A)		Loved	Liked
	Liked		Liked

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.
https://docs.google.com/spreadsheets/d/1T1ysSe11vZaS8AkASAA5EczHarniRCLjDnycMrCXBLc/edit?usp=sharing

Answer 1

The formulas you show for Sheet3 only fill the first row and the first column, so I am assuming that you are manually inputing data in the range Sheet3!B2:D. If that is the case, you are exposed to the risk of row misalignment, and may have to rethink your spreadsheet structure.

One way to do that would be to reverse the dependency: insert a column for "Description" in Sheet3, manually enter items, descriptions, names and reactions in Sheet3, and use formulas to derive all of Sheet1 from the data in Sheet3. You will then no longer need Sheet2.

Sheet1 looks like a report that would usually be totally formula generated, but if you want to be able to enter a selection of names in column A there, you can use a filter() formula in cell B2 there to only show the descriptions and reactions relevant to the names you have entered. The row misalignment problem is not with Sheet1 but with Sheet3, and there is no easy turn-key solution to it.