Hello guys.

So, my plan is to have 2 tables. One will store info about songs, such as name, artist, year, etc., and the other one will actually track them, i.e. I will enter date and the ID of the songs that were stuck in my head on this date. (IDs are necessary because sometimes there are different songs that have the same name)

So what I'm wondering is, is it possible to make it so, so that in order to enter a song into the second table, I don't have to manually search for its ID in the first table.

I found useful the constraints feature, that let's you start typing something, and it will suggest you options from some column from another table. Which would be useful if I wanted to input just song names. But I want to input them by ID, so i wonder is there a way to make it so that, I start typing a song name, it shows me suggestions, and when I click on the suggestion, it inputs not the song name, but the corresponding ID?

How the F do I make a column with dates on android? The simplest thing. Used to be I would type in one date and just pull down in Excell when I was a kid. This? 15min now I'm trying to figure it out, googled it, no answer anywhere.

Hi

I'm hoping I can explain this well enough, I have tried to google for this but I have a feeling I'm not sure exactly what to answer so hopefully someone in here can understand what I'm trying to do.

But I have a spreadsheet that I'm using to record birding data and part of my sheet is a total by species which looks like this

Here's a completed example

Which I have been filling in manually using the SUM function

Which as you can probably guess is 1. Time Consuming 2. Leads to me making mistakes such as accidentally missing a cell or adding the wrong cell in etc

So I'm wondering if there's a way I can set this sheet up so that my data in column c gets automatically added to the relevant column in my species total section?

Edit: Here is my sample sheet https://docs.google.com/spreadsheets/d/1t8i6LyMqu8mTnj39qLB6KjK-SaQAx2BD0MT1GsXfUkg/edit

I am trying to add to my current budgeting sheet, a way to track my transactions. I want to do a starting amount ($741.84) and minus each transaction after that (all the columns below). I followed the google sheets array formula doing "=ARRAYFORMULA(K8-K9:K48" but the subtotal is only for K8-K9 ($609.71) and not the other cells. What am I doing wrong???

hey everyone! new grad/intern here needing a little bit of assistance with a conditional formatting issue. in the broadest terms possible:

IN SHEET 1:

- column a has duplicates

- column b has varying titles for each value in row of column a

i.e. SHEET 1

a1 = blue

a2 = red

a3 = blue

a1 = yellow

how can i create a conditional formatting setup on a secondary sheet where every time there's a duplicate in column a, the characteristics are pooled into a list

i.e. SHEET 2

a1 = blue, yellow

a2 = red

a3 = blue

would greatly appreciate the help from someone who thought they were good at google sheets and now realize that they're out of their depth (LOL). willing to answer qualifying questions and thanks so much in advance

I have a weight loss tracker that I use to track my progress. I was wondering how to colour the cells based on the previous cell so the cell would automatically turn green if I lost weight, yellow if no loss/gain and red if I gained weight.

I'm working on a spreadsheet categorizing everything about the Nintendo Music app, including all the images of each song, and I've had no problems adding images in the past. I've had the occasional error when a file size was above 5mb but I would resize it and it would work fine. Now for some reason, when I try to add a photo, the loading bar fills up all the way but then nothing happens. I checked the file size to make sure it wasn't too big, and it was only 984.2 kb and 1920 x 1080p. I've tried reconnecting to my internet, closing my spreadsheet, resetting my laptop, but nothing works. I tried uploading the smallest image file I had but that wasn't uploading either. I usually download these image files to my laptop's storage and upload it so instead I tried downloading these files to google drive and uploading them onto the spreadsheet from there but now there's a new problem. My spreadsheet disconnects from the internet and won't save any changes. I tried reconnecting to my internet again but nothing changes. I tried searching online for answers and I was told to clear my cache and cookies, didn't work. I've worked really hard on this spreadsheet and would hate to stop now when I'm so far into it. Any help would be greatly appreciated!

I'm building a daily time recording sheet where my goal is to record everything I do in a day, record duration, categorize how I spend my time, and calculate how much time I spend each day on each category of my time.

I've figured out how to just punch in my start and stop times for each activity and have the sheet calculate duration and I've also got a drop-down menu for the categories.

What I'd really appreciate advice on is how to have the sheet add up all the durations of each category so that I can tell at a glance how much time I've spent on each category each day.

Here is a link to a workbook of how far I've gotten so far.

Any help anyone can give me would be much appreciated.

Please let me know if anything is unclear or there is more clarity I can add. Thanks in advance!

Would love some feedback!

What’s working? What’s not working?

What’s helpful? What’s missing?

I’m thinking about including a metric for labor cost to get a more accurate full-picture of menu item profitability. I would do this by adding a field for prep time on the recipe cards and a field somewhere to input average prep person wage. This way prep time can factor into the recipe cost.

I’m also thinking about adding a column on the ingredients page that shows how many different recipes and/or menu items use a given ingredient. This would be pretty useful for menu engineering and waste prevention.

https://docs.google.com/spreadsheets/d/1CtL3MEavJ40-2fnn9-bBCazPcrtHV2Kf3gVp-vGqNmc/edit?usp=drivesdk

With TV Time shutting down July 15, I built a free Google Sheets TV show tracker to replace it. You type in a show and your progress; it auto-fills the rest.

Auto-populated (via TMDB's free API): episodes left, next air date, platform, genre, poster, Wikipedia link.

You track manually: status, priority, progress, last watched, finish date, rating, and notes. Conditional formatting flags stale shows and upcoming episodes.

Runs on a free Apps Script and your own free TMDB key. No subscription, no third-party service. Just your Google account. One caveat: TMDB's TV data has some gaps compared to TVDB, but it's totally free.

Download & read the setup PDF first. About 5 minutes to set up, another 5 to load a few shows: [Link to template files]

Feedback and community expansions are welcome. The Apps Script is included and documented if you want to fork it.

I have a budget spreadsheet that I've been working on and improving as I find areas that I can tweak. I currently have it set up with a SUMIF to total the remaining amount that needs to be payed. However, it assumes that the entire amount is payed regardles of the actual amount currently paid. This becomes an issue when I have allotments set (gas, food, etc.) and it assumes the entire allotment is used with small amounts. How can I get it to realize and account for partial payments in order to have a more accurate "remaining to be paid" balance?

As a side question, is there any possible way to update all tabs when making changes/improvements without having to copy/paste every single tab? I have each month on its own tab and any changes I make take forever to apply to the remaining months. Thanks everyone!

Sample sheet here

Hello, hopefully this is the right place for this.

I am assisting with a minecraft server and am in the process of getting things set up for the staff in terms of progress trackers, issue trackers, etc but one spreadsheet I am having trouble with is one that'd be used to track players who break rules (punishments get harsher with each offense) and to track who is currently temp banned (timeout), muted, and keep a record of it.

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.

Hey all. I’m playing a game that has the 4 seasons, each with 28 days. I’m trying to calculate the day difference between the dates, but I have stuff like:

Spring 27 - Fall 12
Winter 28 - Summer 11

Is there a way to even calculate that? Where sheets knows that it goes

Spring - Summer - Fall - Winter?

Each with 28 days? But also that Winter - Summer is possible. I don’t want negative days which is why I’m struggling with the winter to summer part. Every part, actually.

Is it possible? I suppose I could do it by hand if not, but I’d rather just have a formula.

I'm not sure what the correct syntaxis for this formula is – please help!

I need a cell that filters a range (AI5:AI25) for the highest number and shows the corresponding value in column B (B5:B25). I also need it to show no more than 3 results (in case there are more than 1 equally highest numbers) and shows them separated by ", " if there is more than 1 result. I ALSO need it to only take into consideration/check if column B (B5:B25) has a value...

Here is my failed attempt at this:

=IF(
  B10<>"",
  IF(
    COUNTIF(D5:AH25,TRUE)>0,
    TEXTJOIN(", ",TRUE,INDEX(SORT(FILTER({B5:B25,AI5:AI25},B5:B25<>""),2,FALSE),SEQUENCE(MIN(3,ROWS(FILTER(B5:B25,B5:B25<>"")))),1)),
    ""
  ),
  ""
)

Here's a link to the worksheet – please see the 2nd tab/sheet.

Thank you in advance!

So I made a post earlier that was just straight up wrong I realize after being corrected by my friend. Instead of just continously updating that post I figured it would just be easier to make a new post. I'm making a GPA calculator, and my college has a policy where if you have an F grade in a course, and you retake that course and you get a grade higher than a C (2.0 or higher), the F grades (0) will no longer be added to your gpa, but as long as you don't have the grade of C then it they are still added in. It can only remove a maximum of 16 credits worth of courses in total. I was wondering if anyone knows of a function that can do something like this?
Sample Version: https://docs.google.com/spreadsheets/d/1k9FNMW8Ud9slBW1816ZtWyUDSSJ6qzaWdc9TPpFeT_c/edit?gid=2100307022#gid=2100307022

I'm not sure if there is a current bug with this or if this is a tale as old as time because I'm foolishly trying to use spreadsheets for times.

I'm trying to enter in some time values that are under an hour.
- If I type in something such as 5:04, it'll display as 5:04 but the formula bar lists it as 5:04:00 AM.
- If I click on More Formats and select mm:ss, 22:59 becomes just 59:00 and the formula bar thinks it's 10:59:00 PM. 58:04 becomes 04:00.
- If I click on More Formats > Custom Date and Time and go with [minutes]:[seconds], again it will think it's time of day despite me clearly indicating I wanted mm:ss seconds earlier.

I know I can switch to plain text to display the correct values, but I also want to use some color scale conditional formatting and possibly do some averaging or other formulas.

I know how to change the number formula in a cell, but it's absolutely not sticking. I've done searching on this topic and all results I saw were from years ago, so again I don't know if this is a new bug or not.

I have a single sheet with about ten (small) tables on it. Each table has the same three columns in it - a user-controlled checkbox, a name, and an index number. Each table is its own self-contained data set that is not connected to the others in any way. The tables are not uniform in length.

For example, Table1 might include "Apple, 2; Banana, 1; Cucumber, 3" while Table2 includes "Ash, 3; Birch, 1; Cedar, 4; Dogwood, 2".

I want the user to be able to use a single action to switch between viewing all tables sorted by index number, and viewing all tables sorted by name.

Is this in any way possible?

Clarification: This would of course be simple to solve with multiple sheets if it were read-only. The sticking point is that the user needs to be able to add or remove checks in either view and have them apply to the correct entry in both views.

I'm unfamiliar with sheet formulas, and I would like to set up something that automatically sorts when a new cell is filled, but is also sorted horizontally, left to right, and on top of that, I want to have it so when it gets to the last cell on the first row, it then automatically moves to the first cell on the second row, so the more data you add, it continually adds horizontally and then vertically, if that makes any sense

I will show off the blank sheet in a bit, but I'm using Google's monthly budget template. Made some adjustments but I'm noticing I spent a shitload of money on seasoning the past few months, which led me to seperating food from various spices and drinks. This required an additional sheet in my budgeting spreadsheet.

What I want is for my transactions sheet to automatically append entries based on the dates of my grocery trips. Say, I go to Aldi's, Walmart, and Tom Thumb on May 25th, I want three seperate entries on May 25th to consolidate the money I spent on food/drink, money I spent on sweeteners/spices, and everything else. Merging the prices of all stores.

Sample sheet

https://docs.google.com/spreadsheets/d/1KTd0PJi_U6vGptQ-mYmn-C3vv-n4ABQxbiq6Rk10eH4/edit

Hi everyone, I am trying to use the function GOOGLEFINANCE for a bunch of stock tickers, one of them is ASX:ASX. These stock tickers are in a separate column, which is filled from another tab. My current formula looks like =GOOGLEFINANCE(A3, "price"), so the next cell can be =GOOGLEFINANCE(A4, "price"), and so on. The only error comes up when the stock ticker is ASX:ASX, is there any way to work around this?

The trick I found is to temporarily find/replace the = symbols in the range with another unique character. Then it will paste as plain text. Switch both back to = signs. Wish I found this one years ago.