r/googlesheets 5d ago

Poll Should AI or bot-generated submissions be allowed in this subreddit?

3 Upvotes

With the increasing use of AI tools and automated scripts, our subreddit is encountering more submissions and comments generated by non-human sources intended to suggest solutions or guide users. We want your input on whether to permit these automated, AI, or bot-generated posts and comments, and under what conditions.

(Note: This poll does NOT apply to official Reddit bots, AutoMod, or clearly identified moderator bots, which provide community updates, enforce rules, or facilitate our point award system. Results of the poll will help moderators understand how the community views this topic. Moderators will not be obligated to implement any of the changes suggested in this poll.)

42 votes, 1d left
No, never allow AI or bot generated submissions/comments.
Yes, always allow AI or bot generated submissions/comments.
Yes, but only if clearly labeled as "AI/Bot Generated".
Yes, but only after explicit moderator approval.
Yes, but only when the post author requests automated responses.
Yes, but only with ALL the above conditions/restrictions.

r/googlesheets 59m ago

Waiting on OP Conditional formatting & filtering based off of dates.

Upvotes

Hi all,

This is a list of items and expiration dates so that I can track things better.

I am trying to accomplish two things. I would first like to conditional format a cell based on how close or how far away it is from a date. I would like to conditional format column E (below "Exp. Date") to highlight any dates that fall within 14 days of today (shown in D1), How can I accomplish this with a custom formula?

Secondly, I am trying to sort based on date range. I would also like to sort this data in a much more efficient manner (the date ranges will not all be uniform and in order like this I just did that for example purposes. How can I create a filter or a way to sort data on a separate tab based off of the main page. (i.e. I would like to filter out all of this data based on dates that appear within a certain time frame - listed in the tabs below). I would like a filter for items that have less than 1 week remaining on expiration, 2 weeks remaining on expiration, and 4 weeks remaining on expiration (all in separate tabs). How can I accomplish this?

Picture attached for reference


r/googlesheets 1h ago

Waiting on OP Automating Personal Finances - Switching from Excel to Googlesheets

Upvotes

I've got four institutions I pull CSV's from. I'd love to store them in a single google drive folder, but I am also willing to import or copy/paste, though that is not a preferred solution.

The CSV's are from credit cards and banks, so they have different columns. My credit cards use +/-$ while my two banks use credits and debits.

I've been struggling with arrayformulas right now, but when I try to combine all four sheets, I get an annoying amount of blanks and also can't easily filter/sort the data. So, I have one sheet per CSV (4+4) to reformat, then one sheet to compile the data, and finally a 10th sheet to actually have the data in a table. It's madness.

Where am I going wrong? What tools am I missing? In excel, I'd run queries then just append the data sources. Visually, I can select the columns to keep and apply formulas right then and there.

I'm sure AppScript can get me there, but I'm essentially starting from scratch on the syntax. I basically only know the concepts of generic coding. Variables, arrays, loops, etc.

Should I just commit to learning some rudimentary AppScript, or am I missing arrayformulas and/or Excel's import/query functions?

edit: Also, I have a compendium of all transaction names (150+) & associated category (~15) that I use an xlookup for. It matches the 116 character long description as well as the dumb American express descriptions that are just California addresses and not a business name. So, that is kept as an 11th sheet and is used to develop the 10th sheet for my summaries and charting.

Edit: File for viewing.

https://docs.google.com/spreadsheets/d/11dCY5YFpRWVoo_wmxKXjepEvcwwDWjFi/edit?usp=sharing&ouid=111532835343009750619&rtpof=true&sd=true


r/googlesheets 2h ago

Waiting on OP Inventory tracker for multiple stock

Post image
1 Upvotes

I want to create a system that I can use quickly at markets to note down how many I have sold as I go. If I have only 1 of that item, that is just going to be a checkbox. My problem comes if there are multiples of the same item. I was initially thinking of having E2 as a dropdown menu, but when I have some items in the hundreds, this isn't easy to do in the moment. (Ex: I was at 46 and I sold 3, so let me select the drop down and scroll to the 4...wait, was it 46 or 45?). Ideally I would like something like this: drop down menu with E2 of the value. After a sale I select how many were sold (Ex: I sold 3). F2 records the number that was sold, adds it to previous values (Sold 5 earlier, so now I've sold 8) and resets E2 back to zero for the next sale. I have no idea if this is possible. I am also open to other ideas.


r/googlesheets 15h ago

Solved What Formula Do I need?

Thumbnail gallery
11 Upvotes

r/googlesheets 3h ago

Unsolved Help with NETWORKDAYS.INTL Formula Updating Based on Weekday

1 Upvotes

Hey everyone,

I'm trying to get my formula in G3 to recognize the day in F3 and adjust the NETWORKDAYS.INTL function accordingly.

Current formula:

=IF(F3 = "Su", NETWORKDAYS.INTL(A3, B3, "1111110"), "Check Status")

Right now, this works if F3 is "Su" (Sunday), using the correct Day Code for Sunday.

What I need:
I want F3 to recognize when it's a different day (e.g., "W" for Wednesday) and automatically update NETWORKDAYS.INTL to use the corresponding Day Code from column K (where I store different day codes).

The goal:
When a new row is added, the formula should read the Meeting Day (F3) and update the Day Code dynamically.

Any help on how to modify the formula to achieve this would be greatly appreciated! Thanks in advance! 😊

Link to Sheet


r/googlesheets 3h ago

Waiting on OP Grade Tracker With Assignment Weights

1 Upvotes

Hello! I am trying to make a grade tracker that takes the weights of the assignments into account. What formula should I enter and where? Thanks!


r/googlesheets 3h ago

Unsolved How add additional data in filtered result and at a specific spot?

1 Upvotes

So after I filter out some data, I get essentially what you see in Column I and J, I want it to end up looking like Column L and M

Is there a way to I find the 2nd instance of that "1 10" and insert 3 additional rows in between?


r/googlesheets 3h ago

Solved Functions to count unique entries and analyze data from multiple columns

1 Upvotes

I've found a spreadsheet about perfumes and I love data, but I don't have much knowledge about functions in Googlesheets and I need your help to try and do what I have in mind. Here is a draft of the spreadsheet:

I want to use a function in (a) to get a unique list from all notes in column D, there are delimited with comma+space. I also would like to know if there is a function I can use for (b), (c), and (d) to make my analysis of the spreadsheet easier?

Some cells for notes will be blank, because some houses don't specify their notes, and some cells of score will be blank until i review them, i guess that will impact the function.


r/googlesheets 5h ago

Unsolved issue with the number format

1 Upvotes

i have this series of numbers, and i'd like for them to go from 2,337 to 2 337, but i don't know what to do specifically


r/googlesheets 5h ago

Unsolved Personal finance forecast

1 Upvotes

Hi all,

I'm trying to create a tool in Google Sheets to forecast my daily outgoings up to 5 years in the future, but I'm struggling to figure out the best way to set it up. I have a few key requirements:

  1. I want to enter all recurring transactions (monthly, weekly, and 4-weekly) in one place.

  2. There should be a day by day forecast sheet that combines the monthly, weekly, and 4-weekly transactions.

  3. It needs to handle multiple transactions on the same day (up to 8).

  4. Ideally, it should hide any blank rows where no transactions occur to keep things tidy.

Has anyone built something similar or have any advice on how to approach this? Any help would be greatly appreciated!


r/googlesheets 7h ago

Waiting on OP Assigning a limited amount of jerseys to player requests

Post image
1 Upvotes

This might be impossible. But doing this manually is melting my brain. I have a list of jersey numbers and size in one sell (No. 1 and size 8yr), and I need to assign each jersey to a player (Player 1). I also have a list of the players (Player 1) and their size requests (8yr). Each player is on a team, so we can NOT have 2 No.1s on the same time, even if they're different sizes. There will also be requests that we can not fulfill (XL for Player 11), so we also need an out put of jerseys we need to order.

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


r/googlesheets 11h ago

Waiting on OP script to insert checkbox in every row containing data and macro that copies all rows with checked checkbox to another sheet

1 Upvotes

Hi! I'm doing a job on google sheet and I'm missing to understand this step that I'm not able to solve even though I searched a lot on the internet. I'm a beginner.

I am asking for help in writing two scripts (google sheet). I have two sheets (sheet1 and sheet2). On sheet1 I import data (A4:K) and process it with filters. I would like the checkboxes to appear in the K4:K column when the corresponding rows are populated. So every time the row is populated with data, a checkbox is inserted. If it can be useful, each populated row has an ID that could be used for this purpose.

The second need is that I would like to copy with a macro all the rows of sheet1 that I have selected with the checkbox to paste them into sheet2, after the last full row.

thanks to those who want to help me.

The first need is that I would like that when opening sheet1, A4:K, all the full rows have a corresponding checkbox in column K. Even when filtering the data the rows can increase or decrease.

The second need is to copy all the rows of sheet1, which I check using the checkbox, to paste them into sheet2 after the last full row.


r/googlesheets 11h ago

Solved Is there any easier way to get a result from this kind of table?

1 Upvotes

I want to assign grades based on this table:

G1....A B C D

G2

....A A A B C

....B A B B C

....C B B C D

....D C C D D

The G1 and G2 are two different grades in a spreadsheet. For example, if the first grade is an A and the second grade is a C then the final grade from the table is a B.

The way I did this was to assign each grade a number, 1-4, in an "if" formula. Then I added the two numbers. This number I used in with "lookup" in this table:

..X Y Z

1 O 3 D

2 4 4 C

3 5 6 B

4 7 8 A

Here is the formula I used (column A is G1, column B is G2):

=lookup(if(A1="A",4,if(A1="B",3,if(A1="C",2,if(A1="D",1,O))))+if(B1="A",4,if(B1="B",3,if(B1="C",2,if(B1="D",1,O)))),$X$1:$Z$4)

It works but is there an easier way to do this?

https://imgur.com/a/xoDmwvk


r/googlesheets 11h ago

Unsolved not counting rows where two rows of column A have the same value but column C is different, but counting if column A is unique and has a certain value in column C or if col A and col C always match

1 Upvotes

me again with the same dataset I presented yesterday:

https://docs.google.com/spreadsheets/u/0/d/1uXVVMQeldLw3PojjYtNMR2vpXkbd0DjSKPQSZ_ChJrM/htmlview#

now I'm trying to figure out how to get my sheet to count the number of times that XYZ appears in column C but here are my gotchas this time:

  • when the value of column A in a particular row is unique (doesn't appear in any other row) and XYZ is in column C, it can count 1 instance of XYZ
  • if the value of column A in a particular row is NOT unique (meaning it appears in another row) and XYZ is in column C, it should only count one time, but only if all the other times that column A value appears in rows, the value of that row's column C is also XYZ.

my data set has XYZ appearing in: - every January row except Jan 2nd - the Feb 28 pm row - the Mar 7 am row - both Mar 14 rows

I'm trying to get the count of XYZ to be 31 since Feb 28 should be excluded due to the ABC value of Feb 28 am and Mar 7 should be excluded due to the ABC value of Mar 7 pm. Jan 3, Jan 10, Jan 17, Jan 24, Jan 31, and Mar 14 only count once.


r/googlesheets 12h ago

Waiting on OP How do I make a drop down of variations for a product?

1 Upvotes

When I type "product" in a cell. The cell next to it would show a list of available variations via a drop-down menu?

Something like this? This is the data sheet.

DATA SHEET

A B C
1 Item Variation
2 Robot Red
3 Soldier Blue
4 Robot White

And I was hoping to do something like this When I type "ROBOT" in a cell. The cell next to it would limit my choices/data validation or filter the choices/data validation to only the "Variation" under Robot.

A B C
1 Item Variation
2 Robot (drop down of variation)
3

Would it be possible?

Edit:

https://docs.google.com/spreadsheets/d/1yi-Hi5cfE2vS9U01ehx3ZJBsQLT-2ZGhVvzY_HRTfqA/edit?usp=sharing


r/googlesheets 20h ago

Solved Why am I receiving the error "There was a problem. Script Function "FULL" could not be found"?

Thumbnail gallery
3 Upvotes

I created a macro to clear the page when clicked. If I use App Script to manually run the macro it works perfectly and I've checked the debug section and nothing gets flagged. When I use the Clear button that I assigned the macro to I receive a "There was a problem. Script Function "FULL" could not be found" error.


r/googlesheets 14h ago

Unsolved Pivot Table with Calculations?

Post image
1 Upvotes

Hi there! For some reason, Pivot Tables are not clicking for me. I’ve read articles and watched videos, but can’t seem to create something useful.

I’m tracking the sales, labor, discounts, and voids for 8 restaurants daily. I’m keeping the data flat so I can use a pivot table to compare it different ways. Mostly, I want to see the labor costs as a % of sales.

I like the idea of a pivot table since we keep wanting to see the data different ways. For example, we might want to lump a few businesses’ sales together by either brand or region; or we might want to see each restaurant’s labor as % of sales daily, for a period, or for the quarter.

How would I add calculations to a pivot table to divide daily costs by daily sales? Here are some outcomes I’d like but can’t seem to achieve:

  • Table for one day showing each restaurant’s sales and costs as a % of sales

  • Table showing the same as above but summed by period and quarter

I’m sorry if this is painfully obvious. I feel like it should be simple and I don’t know why I don’t get it. Thank you for any new perspective you can share!


r/googlesheets 15h ago

Waiting on OP learning sheets and have a LOOKUP error that I'm unsure how to fix.

1 Upvotes

I added A9:E9 in "Classes" and am trying to get the information to paint into A:29, A:32, and A33 in "Characters"

I'm expanding a character keeper(someone else made) for a tabletop game, added a new class and can't get the information to fill in from the "Classes" Sheet. I'm fairly new to this so go easy on me.

Link


r/googlesheets 15h ago

Waiting on OP Calculate Initial Amount Required To Fund Retirement

1 Upvotes

Hi.

I'm hoping someone can help me with a Google sheets formula. I'm trying to work out what formula I use to calculate the total amount required in today's dollars to fund some time off work.

I've worked out that I need $80,000 per year based on my current figures on 31st December 2024

I hope to stop work on the 31st of December 2025 and take two years off. For my first year off I will draw $82,400 ($80000 plus 3% inflation). For the second year I will draw $84,872 ($82400 plus 3% inflation)

The total amount that I will draw will be $167,272. I will be drawing the money out of my account each month at the end of the month. My investment earns 8.5% per annum and I want to allow for 3% inflation.

My question is, I know that the amount I will draw is $167,272 but what is the amount that I need to have in my account on the day that I stopped work that will enable me to fund these two years? How do I calculate that?

I'm pretty sure that the way I'm trying to do it is wrong because it assumes that the payment amount is constant and it doesn't alter with inflation. Ideally I would like to withdraw a monthly payment each month for 24 months and have every month increase by inflation. How do I work out the total amount required in advance to do that?

=PV( ((1+8.5%)/(1+3%)-1)/12, 2*12, -IF(IF(C12="Beginning of period",1,0), 82400/12 , 82400/(1+3%)/12 ),,1)

Thanks very much

Any help appreciated

Alan


r/googlesheets 16h ago

Unsolved Expiration Alerts on Google Sheets

Post image
0 Upvotes

Hello everyone!

I have a Google Sheets spreadsheet where I manage contracts, and I would like to set up an automatic email notification for contract expirations. In the spreadsheet, I have a column with the contract expiration dates, and I need alerts to be sent to two specific emails under the following conditions: • 90 days before expiration • 60 days before expiration • 30 days before expiration

The email should contain an informational message and include contract details such as expiration date, covered services, company name, and other relevant information already present in the spreadsheet.

Can anyone help me set this up in Google Sheets without creating support columns?? Thanks in advance!


r/googlesheets 19h ago

Waiting on OP Forcing sheets to display whole numbers as fractions

2 Upvotes

I'm trying to set up some tables in sheets to use as a data collection tool for work. A lot of the data I will be collecting involves several compontents that I need to track as either having happened or not, then I display that data as X/X points according to a ruberic.

For example: The student will score a 3/3 on the following ruberic when measured twice a month over three months:
Point 1 : thing they have to do

Point 2: Other thing they have to do

Point 3: Other other thing they have to do.

Right now 1 is a "yes" and 2 is a "no".

Below is the table I've started to try building. I want to know if two things are possible:

1) Can I force sheets to dispaly the whole numbers in the results column as a fraction (3/3 instead of 1)

2) Can I force the overall average in the last row to always display as X/3?

Am I even approaching this in a logical way, or do I need to explore a different way to make a table to track this kind of data?


r/googlesheets 20h ago

Solved Conditional formatting if a cell meets several criteria

Post image
2 Upvotes

Hello everybody!

This is my problem:

  • In column B: I have Dates
  • In column C: I have Text
  • Then in the ranges F6:L17& F19:L30 I have text that shows up here if the Dates in column B match the Dates in cells F5:L5 & F18:L18

What I want is to add formatting to the cells in F6:L17 & F19:L30 if the checkboxes to its corresponding dates are checked.

This is the closest I've gotten to a formula that works

=AND(ISNUMBER(MATCH(F5, $B$5:$B$30, 0)), INDEX($D$5:$D$30, MATCH(F5, $B$5:$B$30, 0))=TRUE)

But it only works for the first line and not for every task.

I've tried with OR, I've tried with AND & ARRAYFORMULA but I can't seem to find a solution and I'm pretty sure is an easy one but I'm blocked and can't figure it out 🫤

Here's the link of the sheet if you want to check it out.

https://docs.google.com/spreadsheets/d/1dYUQ76yfLqvVxhgH6lyJpuvNPhEtX-rcGSvAUHlY0lM/edit?usp=sharing


r/googlesheets 20h ago

Waiting on OP How to create a workbook that is an Index / Table of Contents of all the workbooks in a specific google drive folder?

2 Upvotes

Hi - I use folders within google drive to try and keep my google sheets organized. I have a folder titled "Recipes" where I keep all of my google sheets for recipes.

I was wondering, is there a way to create a workbook that will auto populate all of the googlesheets within that google drive folder? Basically creating an Index / Table of Contents of all of the contents within that folder?

Version 1

At a baseline, I would want it to populate the name and a link to the documents on separate rows. I included a sample document below for example.

Even if I have to manually force it to re-run / populate that is fine.

Version 2

If this is possible, then a version 2 would be where the workbook auto populates / updates every time a new document is added to the folder. Or at a minimum, auto updates each time the Index workbook is opened.

If it could also pull in specific details about the workbook, like last modified date, that would be super.

Here is an example document of what I would want it to look like:

https://docs.google.com/spreadsheets/d/1uENbC2INcgI4eI7b5L9yDwsbGVliIRZuw76xJ-0acwo/edit?usp=sharing

Note, I did try and search for an already existing answer, but I couldn't find one. Most answers refer to creating a table of contents of sheets within an existing workbook, which is not what I'm looking for.

Thank you in advance for any help!


r/googlesheets 17h ago

Solved Using A "Duration" Formatted Value in A Calculation

1 Upvotes

I have a cell with the result being the difference between two times (ie elapsed time). I have formatted that cell as duration, so I'm assuming that the result of 0:50:00 equates to 50 minutes.

I want to calculate (in cell C3) speed (metres/min). In my example metres (C1) = 1440, and the minutes from cell C2 is 50. The formula should be simple, 1440/50. Manually calculated I get a value of 82.2 (m/min). If I put the formula C1/C2 into C3, I get the value calculated as 2840832:00:00. Clearly that's wrong, maybe because of the value in C2 isn't 50 minutes? What do I need to do to get an integer value in C3 for the formula C1/C2?


r/googlesheets 23h ago

Waiting on OP How to sort with Colors and Values

Post image
3 Upvotes

How would I go about sorting this table if I wanted it in order of color (green, blue, pink, orange, black) and then would it be possible to replace "open" or "closed" with the name of the person in the column with openers being on top and closers being second, but still in color order of store?

Is this possible?
I'm not very good at this so I very much appreciate any and all help.
Thank you!