0

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

2
  • See row misalignment. Dec 7, 2022 at 10:38
  • @doubleunary I can see how that might help if sheet 3 was only pulling in data from either sheet 1 or sheet 2, but it's pulling from both, both are dynamic. This method also relies on filtering out blank rows, but what about column for my other sheet? I don't think this will work for me. If I'm wrong, please submit an actual answer explaining it.
    – Spedwards
    Dec 7, 2022 at 10:51

1 Answer 1

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

3
  • Both TRANSPOSE and ARRAYFORMULA most definitely pull in all the rows/columns. The only thing manually entered on Sheet 3 is the loved/liked reactions. I need sheet 2 because it keeps a list of people, which is also dynamic. It sounds like what you're saying is to only use sheet 1 for the names and then have everything in sheet 3 which defeats the purpose of sheet 1 to begin with... All of this is a hard no. It doesn't solve my problem in the slightest. You're just making everything more difficult.
    – Spedwards
    Dec 7, 2022 at 11:21
  • The transpose() formula you show fills one row, and the arrayformula() you show fills one column. Please confirm whether range Sheet3!B2:D contains manually entered data or formula generated data. Dec 7, 2022 at 11:47
  • I misunderstood your initial statement, your comment is much clearer. Yes, Sheet3!B2:D is all manually entered. I've edited my main question with a link to an example spreadsheet that contains all the formulas I use in my actual sheet.
    – Spedwards
    Dec 7, 2022 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.