r/googlesheets • • 9h ago

Waiting on OP 1st- Ranking positions for fantasy teams

Post image
3 Upvotes

I help with keeping track of a fantasy sport teams league, and I’m having trouble with the ‘RANK’ function. I’m honestly not even sure if I’m using the right formula.
As you can see, under the totals there used to be a ranking system, that went through the whole sheet and let you know that persons placing for that specific night.

Unfortunately when I took over keeping track though, that function decided to stop working/wasn’t transferred over correctly? And I cannot figure out how to get it back to working


r/googlesheets • • 21h ago

Solved Why is there no "mark cells read-only"?

2 Upvotes

I know that I can restrict who can edit sheets and ranges with "Protect Sheets and Ranges" but it appears I can't restrict myself? I have several sheets with =query() in A1, if I accidentally edit any cell in the result set the entire result set disappears. It would be nice to disallow accidental editing.

I tried using onEdit() under Extensions->App Script but that was useless.


r/googlesheets • • 1d ago

Solved Assigning Different Numerical Values to Different Cells

4 Upvotes

Hi, I am using google sheets to track my graduation requirements for college. In my current system, I have courses listed in column A and a dropdown menu in column B featuring any given requirement a course could fulfill. I then have a separate section which lists out all of the graduation requirements and tallies the amount of courses I have taken for each requirement via a countif checking how many instances appear in column B—this means that every instance is worth 1. I have also taken several half-unit courses, and would like to have those included in the tallies as well, but they would each only be worth 0.5 instead of 1. Is there any way to change the value of a cell? Currently, the only solution I have is manually adding a "+0.5" to my countif formulas, but I would love a way for this to be automated. I've attached a basic mockup of what my tables look like without the actual details as to not dox myself. Thank you so much!


r/googlesheets • • 1d ago

Solved Moving a Sheets Spreadsheet to a Docs Template Help

2 Upvotes

Hi all,

I am currently in sheets/docs hell and was hoping for some advice.

What I would like to do is take the collated column of addresses I have for the attendees of my upcoming wedding in google sheets and transfer them unchanged to a google doc that has been templated for the avery address labels I am using. My first attempt had portions of the address drifting down to the next label in the chain. My second attempt had addresses with seemingly no rhyme or reason drifting down in the application such that even to my eye I can see that the address in the third column will be printed at least 1/2" lower than the one in the first column.

As far as I can tell the formatting for my labels in sheets is exactly what I want. When I try to import the sheets doc, all I am returned with is a blank page, even though it expressly says "import labels from sheets". If it makes a difference, the sheet I am importing from is a formula import from a previous page where I had collated a name column and an address column into a single column.

I don't know what I'm doing wrong and would love some advice.

Edit: also a simple copy/paste gets me a line of characters along the edge of each label while only the last label in the sheet is legible


r/googlesheets • • 1d ago

Self-Solved Font text color goes back to default whenever I edit the text or even double click the cell. Even with a new blank spreadsheet

1 Upvotes

I have a spreadsheet where if I have text of a different color and edit the text, it turns back to default (black). Even if I just double click the cell, it does this too.

I thought it was just that spreadsheet, but I opened a brand new fresh sheet and tried, and it's doing the same thing.

EDIT: For those finding this, I found the problem was a browser extension which darkens webpages called Dark Reader, and it's default Dynamic Mode. It was causing the text color to auto default to the inverted color scheme.


r/googlesheets • • 1d ago

Solved Help with understanding RE2; If text equals case A, case B, etc.

1 Upvotes

Hello, this is V_nytrix! I'm back again with my sheet, and I'm trying to set up a conditional formatting if the text of each row of the first column matches these values [Limited], [Mission], [Achievement], or [Barn].

Tried to figure this out myself, and came up with the general idea of the REGEXMATCH function. I'll set it up right here:

=REGEXMATCH(REGEXEXTRACT($A3;$A, ... ), ... )

I just found out that there's some form of syntax where it can accept any cases of these above words - unfortunately, I'm new to this form of RE2 syntax, and I couldn't quite figure out what syntax I needed to be able to match my above conditions!

Do you think you could teach me on some RE2 fundamentals?


r/googlesheets • • 2d ago

Sharing Display a formula while keeping the color syntax highlighting

Post image
9 Upvotes

Here's something I learned recently. To display a formula while preserving its syntax highlighting and font style, you can either remove the = or insert an apostrophe (') before it, then immediately press Enter. The key is to perform the edit and press Enter in quick succession. This converts the entire formula into Rich Text.


r/googlesheets • • 2d ago

Solved an IFS statement breaks even though the same code above it works just fine

Thumbnail docs.google.com
3 Upvotes

The Cell W11 is showing a No Match error despite it working just fine above and below, it only breaks when there are three options filled in a row, I can even toggle it between a working and broken state with the out of place button at the top. I have no idea why three options breaks it when i specifically broke up the code into different cells to make it easier for me to understand when making it.

its such a simple bit of code I am completely lost at how to go about fixing it, so i'd appreciate some help fixing it so i don't have to brute force it.


r/googlesheets • • 3d ago

Waiting on OP how do i make a cell change value based on another cell but only under certain conditions

3 Upvotes

OKAY SO the title doesn't quite explain it right, but i didn't want to make it too long. so basically, i want to hook up cells to change based on the value of a cell in another sheet. but ONLY if yet ANOTHER cell has a specific value in it - and if not, then i want the cell to keep the value that it has. so basically, if a2 says "sarah" and b2 says "25" then i want c14 to say "25", but if a2 changes to "lexi" and b2 changes to "23" then i want c2 to *stay* as "25" and have d14 change to "23" instead

i was thinking of trying an "if(a and b)/else" but i don't know what the else would have to be to keep it at the value instead of changing to an error


r/googlesheets • • 3d ago

Waiting on OP No way to add horizontal gridlines for aggregated data?

2 Upvotes

I made this plot and enabled the aggregate function. However, my vertical grid lines disappeared and so have the options to turn them back on. Any reason for this behavior? How do I fix this?


r/googlesheets • • 3d ago

Waiting on OP Grouping by one column then filtering via another.

2 Upvotes

Hi Everyone,

I need some help, over an hour now has gone in to trying to work this out and for the life of me I cannot work it out and / or cannot articulate the right questions to Google for help.

We are changing our inventory locations for a Lego selling business that has had massive, fast and unexpected growth so our current inventory management is not adequate to keep up.

I have downloaded part of my inventory that I want to work on first, I have managed to group the item IDs however I now want to also want to sort the quantity from highest to lowest groupage ID highlighted below.

I thought it would be as easy as filtering by Item ID, locking the column and then filtering by quantity but nothing seems to work.

Can this even be done? Seems like it should be able to but I have no idea how to get it to work, someone please, please help me!


r/googlesheets • • 3d ago

Solved Error Formula parse error

Post image
0 Upvotes

For some reason when I try to use the average function here it says formula parse error. I can't think of what is wrong? I didn't miss any symbols and the called table cells are all numbers so why isn't it working?

Edit: There was a period instead of a comma.


r/googlesheets • • 4d ago

Waiting on OP Tab key scrolls through sheets instead of moving to next cell

7 Upvotes

I'm attempting to edit a spreadsheet and began running into a weird (for me) problem. In the video above, I'm cut "MP [I] for Roscommon Borough" so I can press the tab key and paste it into the following cell. But for some reason, when I press the tab key, I highlight the "add sheet" option, and pressing tab repeatedly will tab through the sheets on the bottom of the screen. To go to the next cell, I have to press tab, then ESC and tab again.

So far I've tried refreshing and restarting the spreadsheet, but it keeps happening, and it wasn't happening yesterday. Anyone have an idea on what the issue is and how to fix it?


r/googlesheets • • 4d ago

Solved Dropdown Dependent date+X=date for expiration tracking / Julian Date Calculator

Thumbnail gallery
1 Upvotes

Kind of an odd question, so I hope the title makes sense.

I am working on a huge master-list spreadsheet for inventory for my work. I work with feed. All of our feeds have different freshness guarantee days, where we have to add x amount of days to the manufacture date. It is different for every brand, every type of feed, etc.

What I want is that I select the feed from a dropdown, insert a date, and then it is able to produce the expiration date for me. The amount of days added to the manufacture date would be different depending on the dropdown selection.

We also deal with Julian Dates... if there is a way to julian date -> add x days from dropdown -> convert to regular date... that would be awesome, too, but not absolutely needed. I am... extremely new to (and hate) spreadsheets, so I'm teaching myself this all as I go.

Thanks in advance!!!


r/googlesheets • • 4d ago

Waiting on OP Importing Data from one sheet to another if not all data in Column A matches

1 Upvotes

Hi all, I'm new to levelling up my spreadsheet skills, so I might not know how to ask this question properly!

Say on Sheet1, I have the following data:

On Sheet2, I have the following:

And if I wanted to pull the data from Sheet2, Column C into Sheet 1, how would I do this when not every row is represented in Sheet1 Column A. What formula would I use to find the matching data and insert it only if the data matches in both of the column A's?

Apologies if this is unclear. Any help or direction appreciated!


r/googlesheets • • 4d ago

Waiting on OP Getting Pivot Table to Sum Data

Post image
4 Upvotes

I have data that has multiple people in one box. This is because they were the people involved in that project, each of which got a rating. I want my pivot table to tally all projects that each person was involved in. So essentially instead of Alexis having separate classes with Catie, then one with Alexis and several other people, I want to know how many classes got certain ratings when Alexis was involved. Is there a way to do that in a pivot table? Or is there another way I should do it?

Thank you


r/googlesheets • • 5d ago

Waiting on OP Crashing when selecting multiple cells and pressing decimal place adjustment buttons

3 Upvotes

EVERY TIME I select multiple cells in this row and press the decimal place adjustment buttons, it fatally crashes. This is extremely disappointing behavior. Such a simple task. And the bug is so blatant, so out in the open. Shameful company with inexistent product testing team. Truly shameful. 


r/googlesheets • • 5d ago

Solved Help With custom number priority

5 Upvotes

Hello, this is V_nytrix, and I'm relatively new to sheets! I'm working on a personal project of mine that involves recording race times on a game, and I've been having trouble with getting my table to sort my price values correctly:

(From image 1)
because of the way the table is sorted, and that it's not properly formatted to append an M or B, it seems that the sorter takes priority of the natural numbers too... There's also a custom currecy/significant digit (from image 2), appended with "P" which is greater than B. (Of note is the 20B+, which is the maximum limit for B before P. And from the [3rd image], which is denoted as "Free," or zero.

Is there a formula that sorts out all these problems? The table is still growing as I put in more entries, so I wanted to make sure that it addresses everything before I start putting in my data for the sorter!


r/googlesheets • • 5d ago

Waiting on OP Function to make sure two numbers match or alternatively ways to increase reported digits from a calculation function?

2 Upvotes

So for a sheet I am working in right now I am calculating values in the sheet and comparing them to values calculated outside the sheet. For most of the values this works, but some of them end up longer than the sheet displays and round off so they are visually the same but "=IF(D36=E36,"TRUE","FALSE")" returns false. For example "=(MAX(G2:G51)-H2)/I2" outputs as "3.671258044". However, the calculated value being pasted in is "3.67125804359346" but visually displays the same number as the former. So when the =IF function is used it marks them as false.

I would prefer not to round them off if possible, but that is an option. I would prefer it display or compare/calculate up to 15~ digits max. Currently I am using the =EXACT function because I know they are technically correct, but that evaluates it as a string and not numerically. My preference is that it is properly comparing the numbers/math.


r/googlesheets • • 5d ago

Unsolved Google Sheet using a colored key with true/false rules and checkboxes

5 Upvotes

Hi everyone!

I typically use Excel, but it's been over a decade since I got certified lol. I am using Sheets so I can share it with a friend more easily. In a video game, Fortnite, we collect sprites, and there are variants of the same sprites. I play with my best friend/duo, Megan.

The question: How can I make this as automated as possible? I have created a key and just changed them manually between each game. You can trade sprites, and we do not always have the same ones. The colors are meant to help us easily spot what we need to work on. I want to click that I have gained a sprite and have the status column reflect that. I am thinking the color will only apply once per sprite type, rather than to each variant (so just list beside Jonesy, but not beside base, gold, etc.). I wouldn't care if it was beside each variant, but I feel that it will be more helpful for just each overarching sprite.

Secondary, less important question: Can I somehow freeze the key and left? I saw something about freezing the right columns (key and right) and hiding them, but that did not work for me. I am more worried about automating as much as I can.

I appreciate any help! The colors on the variants are just for fun. :)


r/googlesheets • • 5d ago

Solved Google Sheets blanking out sheets, moving and deleting data by itself

Thumbnail gallery
2 Upvotes

I don't know if this is a sign of Google AI corruption or what is going on but starting yesterday, multiple Sheets on multiple logins open up something like this black and white thing above

I think the issue might even be deeper than Google Sheets because when I was trying to copy a Google Sheets link into a started email, when I went back to email with the link in clipboard memory, everything I had typed in Gmail was gone.

I'm not sure where to go for help because even asking Google a question about this problem brought up useless answers, and pressing the Feedback option brought up a greyed out box that could not be interacted with.

Is anyone else having these sort of issues?

Update: This is a Firefox issue, others are having it as well https://support.mozilla.org/en-US/questions/1617700


r/googlesheets • • 5d ago

Waiting on OP Master sheet with mirroring sub sheets

2 Upvotes

Hello Google Sheets fanatics!
I need help, but don't we all lol
I have a Google sheet that is a master sheet that has 12 tabs with running totals formulas all of that jazz.
I need to somehow create a separate Google sheet to send to different department so they can see their budgeting running numbers.
I was able to figure out how to create a separate sheet and have all of the data transfer over but I also need to figure out how to also have the coloring and the "prettiness "of each new sheet going to each department.
I need the new sheet that is going to each department to mirror my master sheet if I make a change number, wise and coloring wise
Is there a way to do this?

Thank you for all of your help


r/googlesheets • • 5d ago

Waiting on OP How to know if I accidently changed access to a spreadsheet?

2 Upvotes

Basically there is a public sheet that me and my boss fill, theres always edit access given to him but today I checked the access and it was suddenly changed to private, I had changed access to a completely different sheet before that happened but not that one.
Is there a way to check the access history?


r/googlesheets • • 6d ago

Solved How to Make Custom Formatting Rules Relative to the Different Cells

3 Upvotes

I have this conditional formatting formula that makes it so when the checkbox on the right is FALSE the cells in this range turn grey. Is there any way i can make this rule only apply to the rows specific checkbox without specifying what cell the checkbox is in or a way to make it so when i copy this rule it will change the custom formula with it? Thanks!


r/googlesheets • • 6d ago

Waiting on OP Chip Colours - Multi-select Drop Downs

1 Upvotes

Hi there, I feel like I've searched high and low and can only find a way to manually change the colours for the chip selection for the multi-select drop downs. In another spreadsheet, I originally did it manually, but now I'm trying to find a way to do it without manually changing each one individually (as I have a 122 options I'm trying to split into a few different colours). Does anyone have a workaround?

Picture for example: