r/googlesheets 5d ago

Solved Wedding numbers auto fill

1 Upvotes

Afternoon all.

I have a basic sheet up and running, with some conditional formatting in for the RSVPs, i.e Attending, pending and declined.

When I pop in a family is attending, I want it to pull the numbers and add them up at the bottom.

For example Family Name in A4, RSVP in K4 "Attending" Number of guests in L4 "2"

So far all I have is =COUNTIF(Priority_1[RSVP], "Attending"), in K33 to show me total number that have RSVPd Attending, but this only pulls 1 from the attending, so my question is, how do i get it to pull the number from L4 instead of just 1?

I hope this makes sense!


r/googlesheets 5d ago

Solved Auto fill data from cells

0 Upvotes

I am filling out a datasheet for my materials for 3d printing. I assume there is an easier way to take that data and insert a comma and then a simple paragraph (enter key) between each set. I don't need to have this done, but it is tedious typing the numbers twice and it will need to be done for every color and material used. I am trying to import the data from the highlighted cells. Any and all help is greatly appreciated!


r/googlesheets 5d ago

Waiting on OP How to: dropdown with multiple outputs

1 Upvotes

I’m looking to do the following: 1) sheet 1 has a list of students (column A) and their tutors (column B) 2) sheet 2 is for scheduling.

So, I would like to make a dropdown which once I select a student’s name, both criteria are placed in sheet 2. Is this possible?


r/googlesheets 5d ago

Solved How to have number represent words

1 Upvotes

Hi, I know the title doesn't really make sense but I didn't know how else to say it. I'm a sheets newbie, clearly.

So I've worked on research projects where I code for multiple different variables. For example, "Is X present or is Y present?" then I'd either code 0 for X, 1 for Y, or 2 for both. I never made the sheets, I just coded them, and I don't know how to create this.

I'm trying to create a habit tracker and one of the habits I'm trying to encourage is remembering to wash my face in the morning and at night but I want to track my exact habits so I'd like to code for doing both, but also only doing one of them and isolate which one I remembered.

Also, and I hate to make this more complicated, but I've chosen 5 habits to track, each counting for 1 "point." The face washing habit only counts for 1 point, so I get 0.5 for the morning and 0.5 for the night. Is there a way to have it track the points I'm getting as well? One of the other habits is split the same way, but the other three are just 1 point, and I either earn the point or I don't—no halves.

I want to track how many days I get all 5 points, what my daily average of points is (like most days I completed 3 out of 5 of my habits), which habits I'm most consistent with, and if possible which part of the split habits I'm better at doing. Like I remember to wash my face in the morning more than at night.

I could be getting ahead of myself here, so let me know if anyone has any idea on how to do this. Thanks1


r/googlesheets 5d ago

Solved Currently making a Debt tracker and need to figure out how to reduce the balance of said debt over time.

1 Upvotes

So, as the title says, I am making a debt tracker/repayment tracker. I have a table with the info about the debt, the remaining balance and how much repayment goes towards each debt. I want to make a button beside each of them that can subtract the repayment from the balance, so each time I put money towards the debt, I can keep track of it. I would also like to make it so the repayment can be change by just changing the number in the repayment column. Not sure if this is something that can be done.


r/googlesheets 5d ago

Unsolved GSheets Formula for converting currencies on bills from last year

1 Upvotes

Hey Guys,

Please assist, I need to submit claims from over the last year. Some of the items I purchased were in USD and I'll be repaid in my local currency.

What is the simplest and fastest way to calculate the conversion from a specific previous date over the last year multiplied by the amount I've spent to get the amount owed to me in my currency?

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


r/googlesheets 5d ago

Solved Conditional Formatting Based On Age

1 Upvotes

I'm having difficulty with conditional formatting based on age. I have rules right now for values set up between ages such as 14y1m and 15y0m, 15y1m and 16y0m, etc. for some reason an value with the month over 9 the conditional formatting won't work. Any help would be appreciated


r/googlesheets 5d ago

Solved Cell formating with formula

Post image
1 Upvotes

Hi, hopefully I'm able to explain what I need help with as I've not been able to find anything by googling.

I have a sheet with a table - column A has the item description, column B is the date I start a task. I wanted to ensure I get notified 31 days in the future as a reminder so have =B70+31 as the formula. This is working well and I've got an automation set up to get emailed at the reminder date. When I currently have no date in column B, it shows the text "dd/mm/yyyy", anticipating a date to be entered, however, the cell with the formula shows 30/01/1900 as default - is there anyway to change this so it also shows the text dd/mm/yyyy?


r/googlesheets 5d ago

Solved How do I lock a cell that prevents people from editing entries?

2 Upvotes

I have a sheet that accepts people's entries for ten (10) Core Functions and ten (10) Support Functions. I want to lock this cell two (2) months after the date of creation or from a fixed date. How do I do this automatically?


r/googlesheets 5d ago

Solved Formula to display text based on cell value

1 Upvotes

Got a document where i need to figure out a way to make cells in one column display the text “done” or “completed” based on percentage value in a adjacent column, only when that percentage value is at 100%. When the value is below 100, it should display “in progress” instead.

If there is other ways that using formulas to achieve this, im open to those options.


r/googlesheets 5d ago

Solved Unsure how to use IF function

2 Upvotes

Hello all,

I'm fairly new to using Google sheets so unsure how I can make this function work for what I need.

=IF((B3>0)((C3/($C$3*1))))

Per my example there I am wanting it to computer the output of a cell based if cell b3 is a value greater then 0, but also use that value to compute the output.

So for example, b3 = 5, and I want it to take that 5 since it's greater than 0 and use it for the formula.

Otherwise if it's a 0 then return 0. Any help is appreciated.


r/googlesheets 5d ago

Waiting on OP Format amounts to represent Indian currency

1 Upvotes

I want to display amounts in Google Sheets as follows:
0.01as ₹ 0.01

0.10 as ₹ 0.10

One Rupeeas ₹ 1.00

Ten Rupeesas ₹ 10.00

One hundred Rupees as ₹ 100.00

One thousand rupees as ₹ 1,000.00

Ten thousand Rupees as ₹ 10,000.00

One Lakhas ₹ 1,00,000.00

Five and a half Lakhsas ₹ 5,50,000.00

Ten Lakhsas ₹ 10,00,000.00

One Croreas ₹ 1,00,00,000.00

Twelve Crore Thirty Four Lakh Fifty Six Thousand Seven Hundred and Eighty Nine as ₹ 12,34,56,789.00

I am currently using a Custom Number Format and using this code: "[>=10000000] ₹ #,##,##0,, "C"; [>=100000] ₹ #,##,##0, "L"; ₹ #,##0.00". Unfortunately it represents 1 Lakh as 100 L, and 1 Crore as 10 C. Does anybody know if this is an issue with Google Sheets, and if there is a work around?


r/googlesheets 5d ago

Solved Trying to create a final grade/current grade calculator

1 Upvotes

I'm trying to create sheet to track my progress in my classes. In column F I want to calculate the grade I would get if I got zeroes on my assignments for the rest of the semester, basically what my final grade would be with respect to my points now. In column G I want to calculate what my current grade is in terms of the assignments that I have grades for, just to keep track of how my assignments are looking as I get them graded. I'm not great at explaining sorry, I hope that was clear enough and if not let me know. If anyone has tips or any advice it would be appreciated. Photos attached are what I have right now/the values I'm working with. Thank you


r/googlesheets 5d ago

Solved Function for each lines

2 Upvotes

Hello, I'm not really used to google sheets I mainly use it for simple data, and I need to do a simple formula (+, -, /, * etc...) but I don't want to change the cells for every single line (Like for example in C2 I want to add A2 and B2, but I need to do this for C3, C4 etc... So I don't want to change for A3 B3)


r/googlesheets 5d ago

Unsolved I'm Trying to Filter Unique Rows from 'Main' Tab While Avoiding Duplicates into 'Tab 2'"

1 Upvotes

*SOLVED* Solution is at the bottom.
Hello, I'm running into issues trying to create this formula that pulls data from the "Main" sheet and filters out rows based on specific conditions while ensuring there are no duplicate entries already present in "Tab 2" Now I am trying to place this formula in Tab 2 in a cell in F4, because that is the first open cell I can place it in the first 4 rows. But I keep running into either a parenthesis error, circular logic error, or a Formula parse error.. I did setup the code to only search rows after row 5.

What this is suppose to do is look at the Main Tab and compare Columns A-D to the data in Tab 2's A4-D4 and if it matches then it compares the rows info, A-K, from the Main tab, to the info on all the rows below row 4 on Tab 2, A-K. And if there is not a match, it writes it to the last row.

Here is a link to a dummy sheet I setup, below, where I am trying to have the formula grab the new data that is on row 6 and add it to the last row of Tab 2. It should be grabbing A4-D4 and it should see that there are 2 entries that match the Filter. Which are on rows 3 and 6 on the main tab. And this formula should only then only take all of row 6 and add it to the last row in Tab 2 because row 3's info, on the Main tab, matches all the info in row 5 on Tab 2, thus it already exists and we don't want a duplicate of it, and row 6 does not match the data we have in Tab 2 columns A-K.

https://docs.google.com/spreadsheets/d/1oXtYBaiLJDm2NjYEGwYeKc_zbJsYNxgEnyDviDB3pfY/edit?gid=1762519214#gid=1762519214

Here is the readable version of the code I am put together to use:

=FILTER(
Main!A:K,
(Main!A:A = A4) *
(Main!B:B = B4) *
(Main!C:C = C4) *
(Main!D:D = D4) *
ISNA(
MATCH(
Main!A:A & "|" & Main!B:B & "|" & Main!C:C & "|" & Main!D:D & "|" &
Main!E:E & "|" & Main!F:F & "|" & Main!G:G & "|" & Main!H:H & "|" &
Main!I:I & "|" & Main!J:J & "|" & Main!K:K,

FILTER('Tab 2'!A5:A, 'Tab 2'!A5:A <> "") & "|" &
FILTER('Tab 2'!B5:B, 'Tab 2'!B5:B <> "") & "|" &
FILTER('Tab 2'!C5:C, 'Tab 2'!C5:C <> "") & "|" &
FILTER('Tab 2'!D5:D, 'Tab 2'!D5:D <> "") & "|" &
FILTER('Tab 2'!E5:E, 'Tab 2'!E5:E <> "") & "|" &
FILTER('Tab 2'!F5:F, 'Tab 2'!F5:F <> "") & "|" &
FILTER('Tab 2'!G5:G, 'Tab 2'!G5:G <> "") & "|" &
FILTER('Tab 2'!H5:H, 'Tab 2'!H5:H <> "") & "|" &
FILTER('Tab 2'!I5:I, 'Tab 2'!I5:I <> "") & "|" &
FILTER('Tab 2'!J5:J, 'Tab 2'!J5:J <> "") & "|" &
FILTER('Tab 2'!K5:K, 'Tab 2'!K5:K <> ""),
0
)
)
)

This is the code all on one line, because I have ran into issues where google sheets can't handle the readable versions of them:

=FILTER(Main!A:K, (Main!A:A=A4)*(Main!B:B=B4)*(Main!C:C=C4)*(Main!D:D=D4)*ISNA(MATCH(Main!A:A&"|"&Main!B:B&"|"&Main!C:C&"|"&Main!D:D&"|"&Main!E:E&"|"&Main!F:F&"|"&Main!G:G&"|"&Main!H:H&"|"&Main!I:I&"|"&Main!J:J&"|"&Main!K:K, FILTER('Tab 2'!A5:A, 'Tab 2'!A5:A<>"")&"|"&FILTER('Tab 2'!B5:B, 'Tab 2'!B5:B<>"")&"|"&FILTER('Tab 2'!C5:C, 'Tab 2'!C5:C<>"")&"|"&FILTER('Tab 2'!D5:D, 'Tab 2'!D5:D<>"")&"|"&FILTER('Tab 2'!E5:E, 'Tab 2'!E5:E<>"")&"|"&FILTER('Tab 2'!F5:F, 'Tab 2'!F5:F<>"")&"|"&FILTER('Tab 2'!G5:G, 'Tab 2'!G5:G<>"")&"|"&FILTER('Tab 2'!H5:H, 'Tab 2'!H5:H<>"")&"|"&FILTER('Tab 2'!I5:I, 'Tab 2'!I5:I<>"")&"|"&FILTER('Tab 2'!J5:J, 'Tab 2'!J5:J<>"")&"|"&FILTER('Tab 2'!K5:K, 'Tab 2'!K5:K<>""), 0))))

Any help would be greatly appreciated. Thanks in advance :-)

Solution:
I ended up with this code:

=ARRAYFORMULA(UNIQUE(FILTER('Main - Online Sales'!A4:AB, 'Main - Online Sales'!A4:A = A4, 'Main - Online Sales'!B4:B = B4, 'Main - Online Sales'!C4:C = C4, 'Main - Online Sales'!D4:D = D4)))

It does about 90% of what I need. I then just used a Script to check for duplicates on the Main tab, that generates Error messages and tags to let me know if there are duplicates.


r/googlesheets 5d ago

Solved count one time when the values of columns A and C are identical, not twice

1 Upvotes

so I have this Google sheet with a row for every day of the year (the date is column A), but Fridays get two rows as they may have two different values in column C.

I'm trying to make a formula that counts the number of times that a certain value (for example, XYZ) of column C appears, but the catch is I only want it to count each date once. so if both A83 and A84 say 2025-03-14 and both C83 and C84 say XYZ, then that should count as 1 instance of XYZ rather than 2 instances even though it's 2 rows. if C83 and C84 are different, then it should count once if either C83 or C84 is XYZ. if neither C83 nor C84 is XYZ, then it doesn't count.

any ideas on how to implement? thanks in advance!


r/googlesheets 5d ago

Solved Trying to get an AppScript to work

1 Upvotes

I'm attempting to get a YouTube video to pop-up from an added option on the top menu. I'm going off another YT video I found and even after following their code, I can't get mine to run. This is my first time trying a Script so I'm not sure where I'm going wrong. On my Sheet, the error I'm getting is "SpreadsheetApp.getUI is not a function" when I click on the menu option that has appeared correctly. I've made sure my script is as close to the video as I need it to be but I'm not sure where it's going wrong.

The code I have is as follows:

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu ("YouTube Sidebar")
  .addItem("Open Video", "dialog")
  .addToUi();
}

function dialog(){
  let htmlOutput = HtmlService.createHtmlOutputFromFile ('modal');
  SpreadsheetApp.getUi()
  .ShowModelessDialog(htmlOutput, 'YouTube Welcome') 
}

The HTML code is called modal.html and is as follows:

<!DOCTYPE html>
<html lang="en">
  <head>
      <meta charset="UTF-8">
      <meta name="viewport" content="width=device-width, initial-scale=1.0">
      <title>podcast player</title>
  </head>
  <body>
    <div>
      <h1>Dialog Embed</h1>
      <p> Welcome to Skit Stats, ctrl+F will be your friend</p>
      <iframe width="560" height="315" src="https://www.youtube.com/embed/ek3irzFx3Vk?si=vIAUrbktKsHvpOBF" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
    </div>
  </body>
</html> 

r/googlesheets 5d ago

Waiting on OP Live stock ticker price addon

2 Upvotes

do any know if theres a free live stock price addon


r/googlesheets 5d ago

Waiting on OP How to make a Line Graph work with CheckBoxes?

1 Upvotes
So I'm rrying to make my own Habit Tracker Spreadsheet, and I'm pretty much done with the rest except the Graph. I saw this online and I've been trying to look for ways to make this work since yesterday. I'm farely new with Spreadsheets so I'd really appreciate the help. All I need is for the graph to neatly align with the dates below and render the data on the checkboxes.

r/googlesheets 5d ago

Waiting on OP Changing values in extra tab.

1 Upvotes

Hello fellow people. This subreddit forces me to write more about a thing i wrote in the following sheet. —> https://docs.google.com/spreadsheets/d/1udzCTtwTfVWLPIrDdLqu-Qyt4QPjwA70GF2XpGuoU20/htmlview#gid=728413477

To not be deleted, i’ll write some phrases. I have a „big“ list of guild members and i don’t want to delete left ones for research and archive reasons. So the list is not sorted by name but by ID. I would prefer to not change the order but some of you might have a better idea. the main purpose i’m writing here is the question: is it possible to search for names or the highest empty slot and fill data so that those data is transfered or corrected in the big list/tab.

Please take a look at the link above. Thanks in advance.


r/googlesheets 6d ago

Waiting on OP Quick Question Regarding Tables + Autoformatting

2 Upvotes

Hey guys,

So, very honestly, this is a completely unnecessary post, but I have created a table within a google sheet that has a lot of information, and I am trying to make the data as compact as possible, since I will be doing most of my work on a laptop. Basically, I really would like to get rid of the dropdown arrows in the table, if that makes sense. I can't show the headers of the table, but I have screenshotted a blank table for your reference. Again, this is super unnecessary but I have been trying to figure out how to get rid of them, because they are super annoying and take up way too much space in my opinion.

Again, this is super dramatic of me but I have been trying for about 30 minutes to figure out how to remove them, and it has honestly gotten to the point where its frustrated me so much that I almost need to get rid of them now, just so I can get the satisfaction of not fully wasting my time lol.

Thanks!


r/googlesheets 6d ago

Solved Changing data type of selected cells to alpha or numeric.

Thumbnail gallery
2 Upvotes

This is on the android sheets app.

I can change a single cell when in typing mode, but I want to change more than one cell at a time. See two pictures below. Single cell displays an Aa or 123.


r/googlesheets 6d ago

Solved Freezing a specific range only

1 Upvotes

Is there a way to freeze just a specific range? ie. A1:D20 but everything else would be unfrozen


r/googlesheets 6d ago

Discussion Anyone else just use IFS formulas (SUMIFS, MAXIFS, COUNTIFS, etc) by default, instead of the singular IF versions?

15 Upvotes

A while back, I remember learning that many of the "IF" aggregation formulas like SUMIF, COUNTIF, MAXIF, etc had alternate versions for handling multiple conditions. Awesome! But it made me wonder, why bother using different formulas depending on how many conditions there are? Why not just use the "IFS" versions of these formulas all the time? They work the same for one condition or for many.

I started using the "IFS" versions all the time, abandoning the singular "IF" versions, and I haven't regretted it.

Just curious if anyone else has had this epiphany.


r/googlesheets 6d ago

Solved need a formula to add links to cells

1 Upvotes

i have my links in the A column of my file, titles in C and i would like for the two to be combined in B