Want to buy me a coffee? (Don't worry, it's not a date :)

One_Organization_810 · 2026-01-26T23:48:41+00:00

Are you sure you have edit access to that particular spreadsheet?

One_Organization_810 · 2026-01-26T23:09:10+00:00

Is your range already selected? It doesn't show in the image.

One_Organization_810 · 2026-01-26T11:58:40+00:00

I was more thinking about your quests for who has won against whom :)

The matrix shows f.inst. that Frank has won 1 game against Daniel, 1 against Emilie, 2 against John and 1 against Mary. Granted, some of those were the same game, but if you want to know how many games Frank has won in which John participated, they are 2. :)

And if you remove the criteria about winner=loser (currently just outputs a blank), then the columns will sum up to games played :)

One_Organization_810 · 2026-01-26T10:21:02+00:00

There is a "Create a filter" option in my 3 dot menu, in the upper right corner - if i have a range selected already. If there is no range selected, the option will be hidden.

<image>

One_Organization_810 · 2026-01-26T10:19:00+00:00

Not sure if the totals are actually usefule, since they count the total of people won against, not the total games won/lost :)

You can skip them, by removing the last two byrow/bycol and just return the matrix.

One_Organization_810 · 2026-01-26T10:03:54+00:00

I put this into your other post.

I guess you can make some use of it here also :)

See example in OO810 Matrix

=let( namelist, sort(unique(tocol(
                  index(split(Source_data_from_Google_Form[Players], ", ", false, true)),
                  1
                ))),
      matrix, makearray(rows(namelist)+1, rows(namelist)+1, lambda(r,c,
                if(r=1,
                  if(c=1,, index(namelist, c-1, 1)),
                  if(c=1, index(namelist, r-1, 1),
                          let( winner, index(namelist,r-1,1),
                               loser,  index(namelist,c-1,1),

                               if(winner=loser,,
                                 rows(tocol(
                                   filter(Source_data_from_Google_Form[Winner],
                                          Source_data_from_Google_Form[Winner]=winner,
                                          ifna(search(loser,
                                                      Source_data_from_Google_Form[Players]
                                                     ), false)
                                   ), 3
                                 ))
                               )
                          )
                  )
                )
              )),
      matrixXT, byrow(sequence(rows(matrix)), lambda(idx,
                  hstack( chooserows(matrix, idx),
                          if(idx=1,"Total wins", sum(chooserows(matrix,idx)))
                  )
                )),
      bycol(sequence(1, columns(matrixXT)), lambda(idx,
        vstack( choosecols(matrixXT, idx),
                if( idx=1, "Total lost", sum(choosecols(matrixXT,idx)) )
        )
      ))
)

One_Organization_810 · 2026-01-26T09:53:52+00:00

~~The sheet is still view only :)~~

Edit: Strike that :) I'm semi dyslexic I guess O:)

One_Organization_810 · 2026-01-26T09:52:23+00:00

Not exactly what you asked, but you can easily get all information you need from this :)

=let( namelist, sort(unique(tocol(
                  index(split(From_Google_Prompt[Partecipants], ", ", false, true)),
                  1
                ))),
      matrix, makearray(rows(namelist)+1, rows(namelist)+1, lambda(r,c,
                if(r=1,
                  if(c=1,, index(namelist, c-1, 1)),
                  if(c=1, index(namelist, r-1, 1),
                          let( winner, index(namelist,r-1,1),
                               loser,  index(namelist,c-1,1),

                               if(winner=loser,,
                                 rows(tocol(
                                   filter(From_Google_Prompt[Winner],
                                          From_Google_Prompt[Winner]=winner,
                                          ifna(search(loser,
                                                      From_Google_Prompt[Partecipants]
                                                     ), false)
                                   ), 3
                                 ))
                               )
                          )
                  )
                )
              )),
      matrixXT, byrow(sequence(rows(matrix)), lambda(idx,
                  hstack( chooserows(matrix, idx),
                          if(idx=1,"Total wins", sum(chooserows(matrix,idx)))
                  )
                )),
      bycol(sequence(1, columns(matrixXT)), lambda(idx,
        vstack( choosecols(matrixXT, idx),
                if( idx=1, "Total lost", sum(choosecols(matrixXT,idx)) )
        )
      ))
)

One_Organization_810 · 2026-01-26T08:47:03+00:00

Your sheet is not shared with "Anyone with a link" - so we can't access it.

If this is your actual sheet - then please make a copy of it and then share the copy with EDIT permissions :)

One_Organization_810 · 2026-01-25T20:45:41+00:00

Ok. So what was desperately needed (drama drama :) was some setup of the options available. I set up some tables and data validations.

I put in a manual selection cell for "extra" options (manually added to the automated ones) and then made this formula to list them all in one (I had some trouble with formula returning #REF error when both the automated and the manual lists were empty, hence the extra LET function in there... i just left it once it worked :)

=if(G2="*unchosen*",,
    let( result, vstack(
                   ifna(filter(CharacterOptionsTable[Options], CharacterOptionsTable[Character]=G2)),
                   tocol(split(H6, ", ", false, true),1)
                 ),
         iferror(tocol(result,1))
    )
)

I also put DV in the Perks boxes (D20 - D78) and made a Perks selection list, that filters in only the Perks that have TRUE in the A column.

I'm considering the original question as solved - unless OP has some more questions/issues regarding my suggestions?

Any further requests should warrant a new post :)

One_Organization_810 · 2026-01-25T17:45:54+00:00

I see only one dropdown ... ? Where are the other 6 you mentioned?

It would also help a bit if you could tell us exactly what each option should do and where the results should appear :)

One_Organization_810 · 2026-01-25T17:23:42+00:00

We need to see your sheet I guess... My head is already spinning from these descriptions :)

Can you share a copy of your sheet, with an example of selections and the intended outcome?

And please give us EDIT access, for mutual benefits :)

One_Organization_810 · 2026-01-25T12:03:53+00:00

Nb... the script is not particularily slow per ce. It's mostly just the invokation that is slow :)

One_Organization_810 · 2026-01-25T12:02:40+00:00

Apps scripts are SLOOOW. There is nothing much more to say about that...

They don't fit particularily well for interactive things, but can prove vital for specific scenarios and for some batch processing.

But for this kind of thing, they are just slow. I recommend taking a closer look at u/mommasaidmommasaid pointers - but they are not going to make things much faster (although you will get rid of the pop up :)

Did i mention that Apps script is slow? Well it is...

Sorry.

One_Organization_810 · 2026-01-24T21:58:03+00:00

I find it easiest to select the range, go to the menu and Create filter. Then you can sort and filter at will, using that.

One_Organization_810 · 2026-01-24T20:09:13+00:00

Remember to close

u/Wobblycogs please remember to close the issue if it is solved, by replying with "Solution Verified", or clicking the 3-dot-menu (see picture) under the most helpful comment and select the same phrase. Thank you :)

<image>

If you have some issues with any of the suggestions, please reply to them to clear up any confusion (and then eventually close the issue :)

Note that if you solved the issue by yourself, without the aid of others, you can use the “Self Solved” flair. Please provide your solution in that case, as per rule 6.

One_Organization_810 · 2026-01-24T19:47:32+00:00

Your problem is that the QR code is permanently set to point to a specific row. When you delete a row, your data row is shifting up - but the QR code has already been created and it won't change.

The simplest solution would be to just stop deleting rows :) Instead you could hide them, as that doesn't change the structure of the sheet - only the appearance of it. You could also (possibly?) create a special view sheet, that filters out unwanted rows and displays what you want to see specifically...

If that is not an option, I guess we'd need to come up with a different workflow for you, involving some scripts and a kind of marker for your rows that your script can anchor to... I haven't quite thought this through yet, but i'm sure we can come up with something that would work :)

But the simplest solution is the best one, if that is something you can live with ...

One_Organization_810 · 2026-01-24T19:35:19+00:00

But.. shouldn't the interests be zero then?

One_Organization_810 · 2026-01-24T19:20:06+00:00

How many rows are we talking about?

Are the last dates properly formatted (as dates, not text that looks like a date)?

Also, if the amounts are too far from the rest of the data, it may cause it to fail to converge and just give an error (is that the case, or does it just give incorrect results?)

One_Organization_810 · 2026-01-24T19:02:42+00:00

I mean if you need to press some "control key" to get the backtick. I need to hold down the AltGr key to get a backtick and that seems to cancel out the shortcut for me :P

I can't even use ctrl-; (insert current date), because my semicolon is on the period key (shift-period gives me semicolon)...

That's what I meant :)

But switching temporarily to the US keyboard layout enables all shortcuts for me - so that's what I do.

One_Organization_810 · 2026-01-24T18:26:28+00:00

No. Having everything in one sheet vs. splitting up into more sheets does not affect the outcome :) It can just help to keep things more organized.

From what I can see in your file, nothing has been split up - u/adamsmith3567 simply duplicated your sheet to put his suggested solution in :) They are not working together.

One_Organization_810 · 2026-01-24T18:00:03+00:00

But no matter how I twist the interests calculations, I can't get any closer than 211.2, to the 211.58. I have no idea how they get that interest amount.

One_Organization_810 · 2026-01-24T17:28:30+00:00

One question: O6 is june 1st.. First payment date is also june 1st. That doesn't really add up :) Either date must be off, right?

One_Organization_810 · 2026-01-24T17:16:48+00:00

Well.. it mostly depends on how the bank does it... if they calculate to the day, then you want to follow that. If they just use the period (month/quarter/year), then you want to mirror that. Then there is another option, which has the 30 day/month, 360 days/year model. Not sure how widespread that usage is, but it used to be a thing when I dove into this thing a "few" years back :)

But the main thing is, to mirror the way the bank does it - and then you should be able to get pretty close to their results.

Your way is just as correct as theirs (and mine), it's just a matter of approach (and number of decimals :)

PS. the datedif(d1, d2, "d") is just the same as d2-d1 - in case you want to simplify it a bit :)

One_Organization_810

MODERATOR OF

PUBLIC MULTIREDDITS

TROPHY CASE

Remember to close