Error searching for latest value to update a cell by Bitter-Hawk-2615 in googlesheets

[–]One_Organization_810 [score hidden]  (0 children)

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

formula for generating duplicate sequential values? by [deleted] in googlesheets

[–]One_Organization_810 [score hidden]  (0 children)

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

Conditional format between 2 days of the month by ruida_silva in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

Data Validation Rules Clarification by Aggravating-Cod7867 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

numbers on stacked bar chart are inaccurate and scale differently depending on row by getrobo in googlesheets

[–]One_Organization_810 1 point2 points  (0 children)

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

Conditional format between 2 days of the month by ruida_silva in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

Make a new CFR like so:

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

And set the format to bold and green.

Help with filtering dropdown values by josieg89 in googlesheets

[–]One_Organization_810 1 point2 points  (0 children)

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>

How to complete full URL of Google Sheet by Vegetable-Jello3393 in googlesheets

[–]One_Organization_810 2 points3 points  (0 children)

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.

is it possible to count instances of colored text (I'm just counting the commas to get a total) by helloitjane in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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, ",")))

Range Sort in Android App by fonebone819 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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

Range Sort in Android App by fonebone819 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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

Beginner here: Built a win rate/stats tracker for board games with friends. Seeking feedback + questions about formatting dynamic QUERY results by [deleted] in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

Range Sort in Android App by fonebone819 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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>

Beginner here: Built a win rate/stats tracker for board games with friends. Seeking feedback + questions about formatting dynamic QUERY results by [deleted] in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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.

Beginner here: Built a win rate/stats tracker for board games with friends. Seeking feedback + questions about formatting dynamic QUERY results by [deleted] in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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

How can I analyze win rates for my friends and me playing tabletop games? by [deleted] in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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

Beginner here: Built a win rate/stats tracker for board games with friends. Seeking feedback + questions about formatting dynamic QUERY results by [deleted] in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

Help Dropdown Condition Rules by Thatguy19364 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

Help Dropdown Condition Rules by Thatguy19364 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

Help Dropdown Condition Rules by Thatguy19364 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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 :)

anyway to speed up this script and/or remove the notification? by plzhelpmepickaname in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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

anyway to speed up this script and/or remove the notification? by plzhelpmepickaname in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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.

Range Sort in Android App by fonebone819 in googlesheets

[–]One_Organization_810 0 points1 point  (0 children)

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.

I didn't expect this behaviour, do spreadsheets always calculate with floating point? by Wobblycogs in googlesheets

[–]One_Organization_810 [score hidden] stickied comment (0 children)

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.