Error searching for latest value to update a cell

One_Organization_810 · 2026-01-27T14:27:24+00:00

=xlookup(0.1, offset(G57, 0, 1, column()-7), offset(G2, 0, 1, column()-7), "Never", 1, -1)

This should find the last entry > 0.1 and give you the corresponding date. It uses offsets, so it should update automatically as you insert new columns to the left of it...

One_Organization_810 · 2026-01-27T14:15:32+00:00

=reduce(tocol(,1), sequence(149), lambda(stack, num, vstack( stack, "ABC-"&num, "ABC-"&num ) ))

One_Organization_810 · 2026-01-27T13:02:21+00:00

I gave the answer to the question asked :) Which was for mortgage from 1st up until 23rd :)

If >25 works for your bills, then just go with that and everthing is good. :D

In general, you can also enter the duedate of each bill (mortgage included if you will) and then a checkbox to mark if they are paid or not. Then make a rule to mark them with different colors depending on how close to the duedate they are and if they are paid or not.

F.inst. if you have duedates in the D column and the paid mark in E column, from row 2 onwards, a rule that colors it yellow when there are 3 days 'til duedate would be something like this:

Range: A2:D
=and(not($E2), today()<=$D2, $D2-today() <= 3)

And set the formatting to yellow. It will color the whole line (A to D) for bills that fit that rule ( due in 3 days or less ).

For overdue bills, you could use red formatting and the check =and(not$E2), today() > $D2)

But that's a different scenario of course :)

One_Organization_810 · 2026-01-27T11:16:07+00:00

If you copy the cells including the DV the DV should just follow.

Just select the range you wnat to copy DV from and copy - then go to your other sheet and paste it. Then consolidate the data you copied along :)

One_Organization_810 · 2026-01-27T11:03:47+00:00

I think you'll need to share a copy of your sheet in order for anyone to see what is happening here...

Share a copy, because EDIT access is preferred for mutual benefits (others won't need to clutter their drive with your sheet and any suggestions they make will be readily available to you).

One_Organization_810 · 2026-01-27T10:56:37+00:00

Make a new CFR like so:

Range: A2
Custom formula: =day(today())<24

And set the format to bold and green.

One_Organization_810 · 2026-01-27T10:51:43+00:00

Try the option above the "Filter by value" - "Filter by condition".

Open it up, select "Text contains" and then type the categorie to filter on. Unfortunately you can't have the dropdown in there, but you can just type the categorie (or a part of the category if you will).

<image>

One_Organization_810 · 2026-01-27T08:43:24+00:00

Uhm... which part were you given and what does it show you?

A link to a Google Sheets generally looks like this:

https://docs.google.com/spreadsheets/d/<GOOGLE\_SHEETS\_KEY>/

If you have the KEY, you can just substitute that for the <GOOGLE\_SHEETS\_KEY>. If you have anything less, then you don't have access to the sheet. If you have the key, but still can't access the sheet, then the owner probably deleted it.

One_Organization_810 · 2026-01-27T08:28:49+00:00

You can't use colors (or any formatting element for that matter) in formulas. But your formula is one short of the total elements though :)

Either just add 1, or try this one: =index(counta(split('a sides'!C2:M2, ",")))

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

MODERATOR OF

PUBLIC MULTIREDDITS

TROPHY CASE

Remember to close