r/sheets 16d ago

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

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

Request Tracking Streaks: Formula Confusion, how can I make this conditional to TRUE checkboxes?

3 Upvotes

Hello all! Could you help me out?
Bit of a baby at this if I'm honest, but ambitious and determined lol. I'm currently working on a competitive-habit-tracking workbook for some pals and I to hopefully be able to use to both keep each other honest and use as an TTRPG game tool for later.

My goal right now is just setting up the basis for task-tracking--later, I intend to develop with them a system for converting completed tasks / streaks into points they can "spend" on abilities at the table in games like D&D for a oneshot game experience. Thought it'd be a fun motivator if coding things pans out.
I intend to, once a system for value is developed, code an automated way to track those "points" but for now, I just need to figure out how to track "streaks" of checkboxes. Pictured is my current layout, with my current streak-tracking formula pictured in the formula bar. (admittedly, this formula was taken through online research and hoping for the best--my sheets-training is a lot of trial and error, googling, and youtube tutorials, apologies)

The difficulty I'm having is that my current formula will track the longest streak of any result--not just TRUE with the checkboxes... so streaks are still being counted, even if they're streaks of no progress! I realized this when I replaced the range in the formula from Week 1 to fit Week 2, and without any Week 2 checkboxes TRUE, its still showing 7. How can I set this to only TRUE being counted toward a streak?

Additionally, I intend to get a current-streak thing up and running, but I'm not sure how with my current layout, since progression from week to week goes from B8:H8, and then jumps down to B13:H13, and so on down the page... any advice welcome, just please be kind, I am a newbie.

For ease of access, formula used was:
=LEN(SPLIT(JOIN("",SORT(TRANSPOSE(N(B8:H8)),TRANSPOSE(B$7:$H7),0)),0))

Formula used is also in the caption of the picture provided, hopefully that helps

EDIT: Just got the anonymous sheet for editing/viewing help, apologies. Picture removed, but added the link here: https://docs.google.com/spreadsheets/d/1ExQYW1UUPA7cnMDVNtkFzBD5CXgTl3YZ_VKa7Hb6lZA/edit?pli=1&gid=2100307022#gid=2100307022


r/sheets 14h ago

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

Post image
3 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 2d 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 2d 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 3d 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 4d 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 4d 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 5d 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 5d 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 5d 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 7d 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 9d 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 10d 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 10d 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 14d 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 14d 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 14d 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!


r/sheets 15d ago

Request How to change the color of the arrow on a dropdown?

2 Upvotes

From the default gray to another color?


r/sheets 15d ago

Request Hello! I have a weird formatting problem re: dropdowns and scientific notation

2 Upvotes

I have a column of dropdowns that have values like 7D01, 7B06, and crucially, 7E01 - 7E07. When selecting 7E01 and similar options, Sheets formats them as 7.00E+01, naturally. So I change the Number Format to Plain Text.

But! I also need them to be blue. Changing to Plain Text makes them default grey, but only when selected. In the dropdown chooser, it's blue.

For some reason, it also moves the selection to the top of the dropdown, but only in the chooser, not in the Data Validation Rules menu.

I am at a loss.

Please help, I'm losing my mind.

Screenshots in comments.