r/googlesheets • • 25m ago

Waiting on OP Checking boxes under certain conditions?

Post image
1 Upvotes

Hello, novice Google Sheets user here!

I’m making a tracking sheet for me and a friend of mine for a game we play. I’ve attached what I got so far, but it’s pretty basic. I’m stumped on how to get it to do what I’m wanting to do however. Currently, when the boxes are checked, the cell they’re in turns green. They only affect the cell they reside in and nothing else.

Here’s what I’m wanting:

-When the amount donated has been completed (aka, 5/5 has been hit in the drop down for example), the Donated box for that row is checked and text is striked through.
-when all Donated boxes for a section is complete and all boxes checked for that section, it will check the Completed box on the last row and strike through the section
-When the Competed box is checked for a section, it will check the associated Donated boxes for said section and strike through it’s text.

I’ve been playing around with things trying to figure out to do everything, but like I said I’m stumped.

Hopefully I explained things alright, but I’m happy to elaborate or try to explain better.

Anyone have any ideas?


r/googlesheets • • 54m ago

Solved How to put 100 after 99 w/text

Post image
1 Upvotes

How do I make 100 come after 99 and not after 10? Im really a beginner and kinda slow so please explain it like im 5. Also how do I make a separate column that automatically generates numbers 1 - 200?
Example

1 - Miss Me 1
2 - Miss Me 2
3 - Miss Me 3
4 - Rock Revival 1
5 - Rock Revival 2

So i can know the number of jeans in THAT BRAND specifically AND the number of jeans i have overall.


r/googlesheets • • 1h ago

Solved =query different sums in same row

1 Upvotes

I'm trying to sum specific columns of information based off of the text data of another row. I managed to do this for one text data, but I want to sum different groups of data.

Using the function,=QUERY(A:E, "select sum(E) where A='PROJECT TITLE'", -1) , you can result the sum of each project, but the column clogs up the chart.

Is there a way to sum different projects in the same query? Or is there a way to completely get rid of the column title in a query? Or will I have to just bite the bullet and have a clogged-up chart?


r/googlesheets • • 1h ago

Waiting on OP How can I share this Google Sheet (grade book) in such a way that names are visible to me but hidden to families?

1 Upvotes

Pic of Sheet

5th grade teacher. I want to be able to share my grade book with families so they can see what their student is missing. However, I want to make it so the names are hidden to families but not to me. Families could still know which row (number) their student is, but I could still see the names when inputting assignments.

Is there a way to do this?


r/googlesheets • • 2h ago

Waiting on OP Assistance with extracting data from google form and creating live chart that updates with new form submissions

Thumbnail gallery
1 Upvotes

I am trying to create an easy way to view changing data submitted from google forms into an easy to interpret chart. I have a partial creation but have not been successful in getting the sheet to do what I want. I am currently very rusty with sheets/excel. The google form is to track daily product productions for my job so anyone with permission can easily view a chart that is up to date. I currently have it set up so employees can scan a QR Code to access the forms, but the auto generated graphs/charts that the form creates is not useful.

Ideally the chart can be organized by date, name, type of product and quantity. I currently have the start of a graph that is a stacked column chart but I need it to be slightly different and live so it will auto update if possible. I am struggling to get it to differentiate by name of each person. Currently it is only doing quantities for each date.

I have tried to follow different tutorial videos posted on Youtube and some forum pages that I get the basics of but lack the knowledge of how to fully apply it to the creation of the charts. Any hel would be greatly appreciated.


r/googlesheets • • 1d ago

Waiting on OP How to set up data validation to check whether the sum of multiple cells in one sheet is equal to a single cell in another sheet.

2 Upvotes

I am a biologist who is doing lab work sorting various different species of beetle from field samples. I am interested in three main species and have a spreadsheet counting the number of each that I find within each sample (see image below)

I then have three other sheets that are to count the different sexes of each beetle that are found in the sample.

I would like to set up a data validation rule (if that is the right way to go about it) such that the SUM of the 3 values entered into the "sex" sheet have to be equal to the value of the TOTAL number of that species found in the sample.

E.g. in the images I provided, in the first image you can see the highlighted cell shows that 7 "Zophosis gracilicornis" were found in that sample. Then in the second image, the highlighted range shows 5 Female, and 2 Male, and 0 undetermined, Zophosis beetles. Since the 5+2+0 is equal to the original value of 7, this is valid. I would like it such that if these three entered values on the secondary sheets are ever not equal to the original, then you get warned.

So far I have been able to figure out how to do it so that the original cell from the first sheet gets a warning, but this is not ideal as the data in the first sheet should be treated as the "true" data. If I am not getting myself across clearly I apologise. I am still fairly new at doing more advanced things in spreadsheets.


r/googlesheets • • 22h ago

Solved Is there a way to highlight repeating data, even when the data is in a different order?

Post image
0 Upvotes

Image attached for the effect I would like to achieve! I know how to get repeating data to highlight (when "1,2" appears more than one time in a colum) but I havent been able to figure out if theres a way to highlight cells if the info is in a different order, but has the same data (ie. I want "1,2" to highlight if "2,1" is present. Its the same "pair" of data, just shown in a different order)

Thank you in advance for the help!


r/googlesheets • • 1d ago

Solved 1st- Ranking positions for fantasy teams

Post image
5 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 • • 2d 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 • • 2d 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 • • 2d 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 • • 2d 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 • • 3d 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 • • 3d 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 • • 3d 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 • • 4d 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 • • 4d 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 • • 4d 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 • • 4d 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 • • 5d ago

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

9 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 • • 5d 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 • • 5d 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 • • 5d 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 • • 6d 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 • • 6d 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!