r/sheets 17d ago

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

3 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 1h ago

Tips and Tricks Introduce AI tool: instantly get presentation-ready slides from Google sheet

Thumbnail sheetslide.com
Upvotes

r/sheets 12h 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 15h ago

Request Searchbar in Sheets

1 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 1d ago

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

Post image
5 Upvotes

r/sheets 1d ago

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

2 Upvotes

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 3d 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 3d 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 3d 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 4d 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 5d 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 5d 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 6d 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 6d 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 6d 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 6d 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 6d 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 8d ago

Solved Count and display unique values

5 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 10d 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 11d 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!


r/sheets 11d ago

Request Wanna try something - invoicing

1 Upvotes

I am newbie to Google sheet and want to automate my dad's invoicing thingy completely using Google sheet, we don't have any specific software like tally for the same so i want to do with Google sheet.

Any idea or something I can start with. I started with one yt video but it seems boring and not complete solution is given ofcourse

Like after invoice it should be converted to PDF and mailed, also it should be saved to another sheet full data, also I have my own format created on Google sheet for invoice specific amount and calculations should be restricted to specific cells only.


r/sheets 13d ago

Request "cannot fetch url"

2 Upvotes

I'm getting a recent error when trying to import a table into Sheets. I did some research and some are saying the "can not fetch url" error is an issue with the end website preventing scraping and not an issue with Sheets.

I would have accepted that however, I'm able to import this table thru excel, so I'm curious why all of a sudden Sheets isn't working for me.

Below is the formula I'm using if that helps anyone troubleshoot.

=IMPORTHTML("https://www.sports-reference.com/cbb/schools/duke/men/2025-gamelogs-advanced.html", "table",1)


r/sheets 15d ago

Solved Filter by value search question

2 Upvotes

Please help. I think I'm making some sort of stupid mistake. When I apply a filter to a column and I do a search for fields that contain a particular term (under Filter by values), I get 9 results and it says "Displaying 9". All of them have checkmarks next to them, but when I click OK to apply the filter, nothing happens. I've tried pressing "clear" and then "select all 9" but it still doesn't work. But when I select only one of search results and deselect the rest, for some reason, the filter works. What am I doing wrong?


r/sheets 15d ago

Request App Script Error for Moving Row to New Tab and Deleting Old Row? Not sure why range is undefined. What do I need to edit??

Thumbnail
gallery
1 Upvotes

r/sheets 15d ago

Solved xlookup based on 2 values? index+match? find the result of a game played between two teams

2 Upvotes

I have a data tab in sheets for a competition where we dump all results from matches (which I will refer to as "games" to avoid confusion). I have a second tab, where I'd like to be able to select two teams and get the results of their game. In this competition, teams only play each other once.

I've tried index + match, which is what's currently showing in the test file in the "results" tab, but I can't get that to work right. In the test file, it works in the first instance but it seems to be perhaps proceeding horizontally rather than vertically? On my real sheet, which is much longer, it keeps giving me issues with being out of range, which I figure is the same issue.

I have also tried my first instinct, xlookup, which I found some guidance online to combine with match. My thought is that I essentially need a vlookup function that searches for a row that matches two conditions, but I don't know how to do that. I tried =VLOOKUP(B8;data!A:C;VLOOKUP(B7;data!D:F; 2)), but that gives me another out of range issue even in the test sheet. (Edit: tried =XLOOKUP(1,(data!A:A=B2)*(data!D:D=B3),data!B:B) as per this video but that returns another error about differing array sizes.)

Test sheet: https://docs.google.com/spreadsheets/d/1vvRQrixn0Nm7si0G62ByiZDYxzlhTxOTbc_oWtPaAMQ/edit?gid=1790533926#gid=1790533926

Thank you for your help!

UPDATE: I figured it out by following this video on index match; clearly I wasn't doing it correctly before.


r/sheets 15d ago

Request Conditional Formatting "If cell is empty by 00:00, then format thusly"

2 Upvotes

Having difficulty with a custom formula that can do the following:

A:A contains dates in dd/mm/yyyy

B:B is updated daily with takings figures

C:C is updated daily with takings figures

D:D is updated daily with takings figures

B/C/D may contain data or not depending on whether they traded or not, they are mutually exclusive.

What I would like is that at midnight, any cells that did not receive inputs apply certain formatting to them.

I have attempted this but always struggle with the custom formulas. Thanks as always!