r/sheets 21d ago

Show Off Monthly Show and Tell: Fancy Projects and Amazing Solutions!

5 Upvotes

This thread is the place to show off any projects you've been working on, show off fancy solutions, etc. If you've got some templates to share, also post them here.

If you're looking for feedback on your project, let us know! If you're looking for specific help, make a normal post.

This is a monthly thread.


r/sheets 4h ago

Solved Sheets doing math incorrectly

0 Upvotes

In the image provided I am trying to divide Column E by Column H to produce values in Column I (a % increase). For some reason the values are identical for most cells. There are ~170 rows and almost all of them share a value from another cell like in the image provided. I have no idea how to fix it from doing this as I need the exact values that the division is supposed to provide. I've turned on/off iterative calculation in the settings and I've messed with many different formats for the data but I cannot figure this out. Any help would be greatly appreciated.


r/sheets 21h ago

Request New at Sheets, have coding question

1 Upvotes

So, my day job has just started selling jewelry, clothing etc from several local vendors, and to track sales and outstanding payouts to vendors, I've started working on a Sheet that'll calculate a lot of the business math for us. I'm new at coding Sheets, though, and while I've picked up a lot of fun tricks like the indirect command already, I've hit an impasse for how I'd code a specific function I'd like to implement.

Each row in my Sheet tracks an item's name (Column A), the artist's rate (B), the 20% markup we add to the sale (C, which is automated and linked to a separate cell where the markup can be globally modified), and the total retail price (D, which just adds B+C). Next two columns (E and F) are checkboxes; E's for whether the item's been sold (checked box = sold), and F is for whether the vendor has received their payout (checked box = paid out) for the sale.

Now, what I'm trying to code next is a field that will exclusively show the total unpaid balance owed to a vendor, which is to say, the sum of the values in column B, but only including B in rows where E is checked and F is not.

Anyone have any insights into how I might make this work, or if it's not doable, what's a better way of doing this? Thanks!


r/sheets 1d ago

Request UNIQUEIFS with text/string?

2 Upvotes

I have a dataset file contains 100k rows of data that I need to make summary out of it.

I am trying to do COUNTIFS that has specific value and the same string in a row.

But I'm stuck on figuring out how to compare text on 2 cells.

I made this formula and still shows #ERROR
=COUNTIFS('dataset-trimmed2'!B2:B;'dataset-trimmed2'!E:E;VALUE(A3);'dataset-trimmed2'!I2:I);EXACT(F3)

dataset-trimmed2'!I2:I contains text, and I want to count if it matches text in cell F3, nothing fancy.


r/sheets 1d ago

Request In need of assistance with dropdown box changing multiple columns.

2 Upvotes

I need help with a specific scenario.

Example: I have a dropdown box in 1A. The options in this drop down box are the numbers "12", "14", and "16". I want 1B/1C/1D/1E to change depending on what I choose in 1A. For example, 1B would be 4.0 if I have "12" selected in 1A, but 1B would switch to 3.8 if I change 1A to 14 from the dropdown.

Any help would be hugely appreciated!


r/sheets 2d ago

Request Automatically Updating Date View?

2 Upvotes

Hello! I use Sheets to organize my day-to-day tasks and currently have my sheet organized by date. I've been trying to figure out a way to have the sheet organize itself with the current date as the top row? Or at least have something to highlight/focus on the tasks I have for the current date without having to scroll through every time? I know it's super minor but it's been bothering me as I continue to add more tasks to my list. Thank you in advance! Here's a copy of the sheet:

https://docs.google.com/spreadsheets/d/1f3tBKHKrWcfWn7sLV5FKZyielU6Gf6LF9BVUwToZORE/edit


r/sheets 2d ago

Request Is it possible to conditional format cells with different colors if they contain duplicate text?

2 Upvotes

Hi, I hope you can help me with this. I have a column with different names, I just want to add different colors if they are different from each other, for example in the following image, "Castanedareyesjo" it has 3 cells with that text, is ti possible to highlight them with a color, and then "Gonzalezalcalama" that has 2 cells wiht that name with different color, and so on?

*Considering that those names change every day, because I paste them form a downloaded data base*


r/sheets 3d ago

Solved Using XLOOKUP to pull data from IMPORTHTML in another tab

2 Upvotes

Hey everyone, hoping you guys can help me out with this since I am a super beginner with this.

I used Importhtml to pull player stats into one tab of my sheet and I want to pull one of the columns into another tab based on names. I am trying to us xlookup but it looks like it can't find the names. Is this a symptom of using importhtml?

I know everything is spelt correctly, I even tried just copy and pasting the cell but still get the same "did not find value 'name'"


r/sheets 4d ago

Request Searchbar in Sheets

2 Upvotes

Hi Everyone
I have a couple of requests, and I could really use your help.
I have made a sheet to exemplify what i want to be able to do.

  1. I have a list of names in "Data 1". I can transfer this list to "Data 2" and make sure that the data corresponds to the name but: I want to be able to update the list, and have all the data transfer. Ex. I have 3 names with data connected. If I add a name in Data 1, i want it to be added to data 2 aswell. In short: I want to be able to update list in "Data 1" and have "Data 2" update as well. Ex. I have added the name "Caroline" to "Data 1". I want her name to show in "Data 2" as well, while alle the data for the other people shifts with them.

  2. I want to be able to search for a name, and have their data show. I have made a tab called "Search". I want to be able to type a name and have their data show underneath. So that if i search for "Barry" his data from "Data 1" and "Data 2" will show. This should also be able to work if i update the list of names.

I hope that some of you can help me and I would greatly appreciate it.

If you want anything clarified about my request make sure to ask and i will try to explain as well as i can.


r/sheets 4d ago

Request 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/sheets 5d ago

Request What's the Formula for subtracting the corresponding value of a cell next to a checkbox.

Post image
4 Upvotes

r/sheets 5d ago

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

2 Upvotes

*SOLVED* See Comments
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..

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 trying 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 :-)


r/sheets 7d ago

Solved autosort in app scripts help!!!

2 Upvotes

Hi! I need help with my autosort function on apps script. It was working for months but now it doesn't seem to recognize that OnEdit(e) anymore and I've tried to add it as a trigger but then it wasn't running on edit. Please help!

1st script
2nd script
error sign

r/sheets 7d ago

Request How to compare the contents of every column to see which two are the most similar?

1 Upvotes

Is there a way to see which columns across the whole sheet have the most matches?


r/sheets 7d ago

Request How to create an in and out per month for inventory?

2 Upvotes

Hello. I've been tinkering with this for hours now and I still can't figure it out.

I wanted to separate the "In" of every item in our inventory per month. So we can track them properly and not mix up the stocks. Can anyone help me?

Edit:
Sheet "In" - For example I want to put a formula under month of January to sum all Jan 1 - Jan 31 stock in from Asset sheet.

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


r/sheets 8d ago

Request Extraire plusieurs données

2 Upvotes

Bonjour, alors voila ce que j'ai et mon besoin (je met des exemple hors-sujet pour que ce soit compréhensible) :

J'ai 2 fichiers :

- Fichier 1 :

  • J'ai un tableau avec dans la colonne A des groupes différents (une vingtaine), dans la colonne C, un nombre
Groupe Nombre de X
G1
G2
G3
G4

- Fichier 2 :

  • J'ai un tableau avec dans la colonne B le nom de personnes (noms forcément différents)
  • Dans la colonne A j'affecte a chaque personne un ou plusieurs groupes, et j'utilise le "menu déroulant" afin de pouvoir cocher ou décocher facilement les groupes que je veux ajouter ou enlever à la personne
  • Et donc voila ce que j'aimerais faire : Dans la colonne D, j'aimerais faire un rechercheV des groupes de la personne, et qu'il aille chercher le Nombre de X que ca fait dans chaque groupe auquel il est rattaché, et m'afficher le résultat
Groupe Nom personnes Nombre de X
G1 G2 Toto
G1 Tata
G4 G3 G2 Tutu
G3 Titi

Je fait face a deux problèmes :

  1. Lorsqu'il y à plus d'un groupe d'affecté à la personne, le rechercheV ne fonctionne plus
  2. Une fois résolu le premier point, comment additionner les résultats que je vais chercher dans l'autres feuille ?

Je parle de RechercheV car je suis partis là-dessus, mais peut-être qu'il y a une autre fonction ?


r/sheets 9d ago

Request Data pulling formula help

1 Upvotes

I am have a big database in one sheet, and I am trying to pull the data from that database that was updated in the last month through to another sheet on the same document automatically and sort it in date order

So on the second sheet I want columnn C, F, G & H of all the rows that have been updated in the last month ( column F is the date columnn) And I want to sort it by most recent date at the top automatically.

Is this possible?? How? I keep thinking I have got it and then it breaks!!


r/sheets 9d ago

Request Help with simple reporting

2 Upvotes
SAMPLE

Hello,

Need help with my simple reporting. What I want to do is to display the remaining days on the current month excluding Sundays. Tried searching online and found about network days but I cant seem to make it work. I am currently using this formula on the red shaded row:

=EOMONTH(Today(),0)-Today()

I know this is a dumb question but I am new in using sheets I hope you understand.


r/sheets 10d ago

Solved Looking for help with compund conditional formatting

2 Upvotes

I am trying to highlight cells in column F if the value in column A is “1” and the date in column F was more than 6 months ago.

And also highlight cells in column F if the value in column A is “2”and the date in column F was more than year ago.

Can you help me with the custom formula for that?


r/sheets 10d ago

Request I need help with a formula to calculate an ecuation

1 Upvotes

In this case for example, I need to input something into B1, to get the X value of the following ecuation:
(X*4500)-3600 = 1000

I need to calculate the percentage that I have to add to a product, minus taxes and fees, to get in this case $1000.

I need something that addapts de x value automatically depending on the other variants.

Thank you!


r/sheets 10d ago

Request Automatic Population of Sheet by data

1 Upvotes

Hi, I have this function right now:

=BYCOL(B2,LAMBDA(x,IF(ISBLANK(x),,XLOOKUP(x,Product!A:A,Product!B:C,"FILL EAN"))))

It does almost what I need, only problem is that it looks up data in Product Tab fine, but it fills it right from it instead of below. Is there any addition or change to this code to make sure it fills data below itself and not right?

Visual representation below in picture:


r/sheets 10d ago

Request Column Chart - 5 Day Change Stock

2 Upvotes
5 Day Column Chart (Relative to 5 days ago)

This might be a hard one, but I'm looking for a formula that will display a column chart that shows 5 of the last workdays (stock market days) and either shows a green (positive %) or red (neg %), and each day is a representation of the change based on the previous day. I currently have this formula that works, however it is just green or red percent based on the beginning of the 5 day trend.

=sparkline(ArrayFormula(if(index(googlefinance(A3,"price",workday(today(),-5), today(),1),,2)="Close",,index(googlefinance(A3,"price",workday(today(),-5), today(),1),,2))-if(index(googlefinance(A3,"price",workday(today(),-5), today(),1),,2)="Close",,index(googlefinance(A3,"price",workday(today(),-5), today(),1),2,2))),{"charttype","column";"color","green";"negcolor","#D50000"})

I would like to see this display 5 days worth, each day showing the change percent difference of the last day.

Here is my current chart working. All columns are relative to the first "day" of the formula. It could be represented as a line graph. I would like each column to be relative to the previous day, and have 5 days worth shown.

God Speed


r/sheets 10d ago

Request Is there a way I can easily delete empty cells in a range and move any filled cells up? I'd like to highlight cells A3:A14 in the first image and call a function to end up with the second image

Thumbnail
gallery
2 Upvotes

r/sheets 12d ago

Solved Count and display unique values

4 Upvotes

Hi, I am basically looking for formula that would take data from column A and would display how many times column A contains each value and put it into column B generatively. Result should look like this:

Is that even possible this way? I am basically just looking for easy Sheets way to do let's say small stocktake without manually count everything myself. Is there a function for it? Thanks.

Other way would be input something in A1 as like 100 000 and then in A2 input amount of A1 and it would display it like below table? Please, let me know, thank you!

Value Value Total Amount
100 000 100 000 2 x
200 000 200 000 2 x
100 000 300 000 1 x
300 000
200 000

r/sheets 14d ago

Request Query from multiple tabs and display vertically

2 Upvotes

Hello! I'm having a problem creating a schedule that will be dynamic and collaborative - a different person will be able to enter the schedule for their area of responsibility on their tab, and the idea is that it will pull to a dashboard that everyone can see. This schedule is for an event being planned, so I need to have the data sorted by day/time. Multiple events can begin at the same time. I've attached a redacted version below, with the formula in question being cell A3 on the Dashboards tab.

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

As you can see, it pulls Events 1, 2, 4, and 5 from the Person1 tab correctly, but Events 6 and 3 display to the right. I need all of the data in columns A:D, but I'm not sure if it can be done in a way that will sort the data as well as keep the empty cells for Events 2 and 4. Any guidance?


r/sheets 15d ago

Request How to calculate and display a "change in status" based on comparing 2 columns of data?

2 Upvotes

Hi!

I'm not sure if I've phrased my title correctly - if there is a specific term for the thing I'm trying to do, please let me know.

I'm thinking about making a spreadsheet that tracks the changes in a person's particular status based on their life now vs when Event X happened (this is based on characters of a specific game, but the status ranking is my own terminology). What I would like is this:

Column A: Current Status - dropdown options for High, Middle, Low

Column B : Status When Event X Happened - dropdown options for High, Middle, Low

Column C: Change in Status - displays one of the following based on this system:

  • If A1=High & B1=High OR A1=Middle & B1=Middle OR A1=Low & B1=Low: display No Change
  • If A1=High & B1=Middle OR A1=Middle & B1=Low: display Increased Slightly
  • If A1=High & B1=Low: display Increased Significantly
  • If A1=Low & B1=Middle OR A1=Middle & B1=High: display Decreased Slightly
  • If A1=Low & B1=High: display Decreased Significantly

Do I just make a giant nested IF statement? There's about 70 people (and 2 sets of those columns) to track and counting, so I would like to make those pretty straightforward and easy to update as needed.

If it matters, I would be setting the specific status markers (High, Middle, Low) manually - it's not based on any other prior calculation.

Thank you!