0

I've created a spreadsheet to keep track of the progress that my friends are making in a competition we're doing. I would like google sheets to automatically update which one of us comes in 1st, 2nd, and 3rd based on the values in other cells. For instance, if I were to come in first, I would enter "Win" into a cell, which sheets would then read and compare to the empty "Win" cells of my friends. If my cell is the only one with "Win" in it, sheets would give me 1st. Then when either of my friends finish and type "Win" into their respective cells, sheets will read the cells again and assign 2nd, and finally by default give the last friend 3rd. This dilemma isn't critical to the spreadsheet or anything, I'd just like to know if it's possible for the sake of knowledge and to have the spreadsheet do something cool. I've tried nesting multiple IF, AND, and OR functions and thought I was getting close, but it never completely worked. If anyone knows if this is even possible in sheets or knows of a solution, any help is appreciated. I've attached a copy of the spreadsheet.

https://docs.google.com/spreadsheets/d/1VNMGe_gytt2CuWE0uhdp5NbDADl6F49kK4saG_2X2Rk/edit?usp=sharing

Improve this question
2
  • yes it is possible ... share your sheet
    – Daniele
    Commented Apr 29, 2022 at 10:53
  • Hi Daniele, thank you!! I edited my question to add a link to a copy of the spreadsheet. I'm looking for the rankings to populate in cells K56, M56, and O56 based on the info I typed in the original post. When I referred to the "Win" cells, I meant cells K49, M49, and O49 specifically.
    – Tyson Dunn
    Commented Apr 29, 2022 at 17:34

2 Answers 2

Reset to default
0

You seem to expect a spreadsheet to magically recognize the order in which "Win" was entered by the three users. That is not how spreadsheets work.

The easiest solution would probably be to enter a timestamp instead of "Win" in the cells. To enter a timestamp, press Control + ; or ⌘;. See the keyboard shortcuts help page. You can then use a sort() formula to show the standings, like this:

=transpose( sort( transpose(K$38:O$38), transpose(K50:O50), true ) )

If you want to enter "Win" instead of a timestamp, you can use an onEdit(e) script to create timestamps in a helper column. If you are interested in pursuing that, take a look at scripts such as yetAnotherTimestamp_, timestampMultipleRanges_ and timestampMultipleColumns_. Search this forum for many more examples.

Improve this answer
2
  • I have a fair grasp on how spreadsheets work, but obviously there's a lot of things that I don't know about certain functions and what will and won't work. Which is why I asked, to learn something new. But, I really appreciate the help nonetheless. I think that's a good solution and we're gonna roll with it. I'd upvote but can't. Thanks!
    – Tyson Dunn
    Commented Apr 29, 2022 at 18:07
  • The WikiPedia article on spreadsheets explains this in a concise manner: A "spreadsheet program" is designed to perform general computation tasks using spatial relationships rather than time as the primary organizing principle.
    – doubleunary
    Commented Apr 29, 2022 at 18:15
0

Instead, you can enter the time at what time this task was completed.

After the entries are made, you can use the =rank() function to rank based on the time field.

Improve this answer

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.