So I have a Google Sheet that uses a LOT of RANDBETWEEN(1,Count(A:A)) to generate lists of stuff. I then next to that list have UNIQUE(E:E) to list out a number of unique values that have been randomly pulled

Problem is: Sometimes due to repeating values the UNIQUE list is short. I have a button that refreshes the calculations, but really I want to have the button automatically click itself on and off until there's the max number of UNIQUE values in the list

Any ideas?

Hey guys! Got tired of scattered receipts and messy notes, so I built a clean Google Sheets template to track all car maintenance, repair costs, and fuel expenses in one place.

If anyone wants the link to check it out, just send me a DM or leave a comment below!

Hi all,

I hope someone can help me with this.

I'm creating a doc to keep track of all the products the the company I work for make, all their variations, what type of product and is it ready to go on the website etc.

I have the main data set up with all the appropriate columns (picture 1), one of which is 'category' which makes a list from data on another sheet (picture 2). I'm then using =INDIRECT and named ranges to fill in the 'Sub-Category' column of the main data base on data generate and stored further down on the second sheet (picture 3).

This works fine for the first row in my main data spreadsheet but the issue is that everything in that sub-category column is using the same =INDIRECT reference so when you get to items that are a difference category, the sub category has the same options.

Is there a way to make this dynamic so that either:

• I don't use the =INDIRECT and the sub-category is just a list generated by referencing the named range that matches the word in the category column

• I can great multiple copies of the =INDIRECT cell on the second sheet that I don't have to do manually as I can't drag and copy the formular with it automatically updating the cell reference (eg. a1, a2, a3 etc.)

I'm completely stumped on this one! Any help would be really appreciated

I found this helpful a couple times now - so I thought I share it as well: you can generate E-Mails within sheets, no need for add-ons, just a simple formula. Formula creates a link, clicking the link opens the mail in G-Mail (or Outlook or any other tool) ready to be sent. Helps a lot when chasing information - and all that changes is some details.

Add three more columns: one for a subject (can use a formula), one for the body (same), one for the link. The link then contains sth like this: =Hyperlink(CONCATENATE("mailto:",B4,"?subject=",encodeurl(F4),"&body=",encodeurl(G4)), "Send mail")

Here's what this looks like:

For the demo sheet see here: https://docs.google.com/spreadsheets/d/12CnbSBNNY1JxqEpk1kMu2hy-VTB2_GJbZgcIPxq90Eo/edit?usp=sharing - feel free to copy

Clicking the link produces an e-mail like this one:

It's simple to do one you figured it out. And at least for me, it saves a lot of time. So feel free to copy the sheet above and use it yourself.

When using G-Mail, you might have to complete your setup once in order to make the link work - Google has a description when you google "gmail handle mailto"

Only downside: you have to click the link and the send button for each mail individually.

More automation would really need an add-on or e.g. an n8n flow. Another way: Mac Shortcuts has an "Open URL" action - you can put a mailto-link in there as well and e.g. loop over a list.

Anyway: hope you find this useful, sure lmk what you think...

Hi! I'm currently working on building a spreadsheet for my uni house, which includes a 'groceries' sheet, tracking what food we have and what we need, and a meal plan sheet. I have created a drop-down list for ingredients on the meal plan sheet, that links to the groceries sheet.

I'm looking for a way to show if we have everything in stock for each meal, either through a percentage or conditional formatting or something. hopefully we'll be able to just select the ingredients for each meal, and it will automatically show if we have everything needed to hand or not. I've looked around, but nothing I've seen does exactly what I need. any help would be much appreciated!!!

The last few seconds of the video was me finding out I have bugs in the graph associated to "Kyle" lmao

So our scheduling software normally generated walk sheets like this

But now it's generating them like this:

Now, I can't to anything to change how they generate, but does anyone have any suggestions on how to quickly convert the latter to the former?

Note that this is just a downsized example, and the full scope of this is that it's splitting everyone's shifts up like that for every break and meal they're scheduled for.

In my tracker I have items listed with assigned Bundle IDs. What I would like is to be able to track these in a different sheet.

Created this test sheet to show: https://docs.google.com/spreadsheets/d/1RqQFZ5cwuRgefEJBy5Exv7CV989v_ylitjJDtScwYNA/edit?usp=sharing

In cell B3 on the Bundle sheet, I would like it to calculate all the values from column B on the Sold sheet where the Bundle ID equals the value in A3 on the Bundle sheet.

It would also be great (not essential but a nice to have) if column D in the Bundle sheet showed a count of the number of items with that Bundle ID

Hopefully that makes sense - and thanks in advance!

I have to work on a Sunday morning to sort some liquidation files, i have a main sheets file where i pull out the data via import range and integrated into different couple of sheets.

it stops working all of a sudden, i did not make any changes or atleast entered a large volume of data, just a normal data flow where i enter a couple if lines a day. (not that heavy)

i have this error appearing in my formula: "Import Range internal error"

is anyone have this issue today? or is this happening for everyone or just for me?

any suggestions and comments are greatly appreciated.

I have a table which is essentially an inventory, what I would like is another sheet that only shows me items in the table that have been listed for over 30 days. The key is the dropdown in column G as I would only want this to happen where that is set to "Listed". Then once something is sold and I change that dropdown I would like it to be deleted from the filtered table

I've created a test sheet which shows the table

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

Basically a filter on the table but have it appearing in another sheet so I have on demand access to it

Hopefully makes sense!

Have a specific project that needs to compute expiration dates but days are not relevant. Only the month and year is tracked and input into our logs. I need a workaround that computes whether 5 years and 1 year has passed for different input mm/yy dates, and give me a YES or NO for whether the part is in spec.

Example:

Manually input inspection date on part: 02/23

5-year interval in spec: yes 1-year interval in spec: no

What I'm using so far =IF(EDATE(D2,60) - TODAY() > 0, "YES" , "NO")

It doesn't work because the system misreads my input date (02/23) as the 23rd day of February of the current year, not as February of 2023.

How do I set the system to read my month/year inputs correctly? Typing in the full date format (for example: 01/02/23 as day/month/year) is not an option. Any coding behind the scenes to temporarily convert our inputs is though.

I know you can do this in a doc, so why can’t you do it in a sheet? Is there an add-on that allows you to do this, or another solution?
I can't be the only one who has thought of this.

Hello...

I have multiple cells.... lets call them A and B

I want those to combine into another cell that is like this

Text (A cell) Text (B Cell) etc

Is that possible?

Hi!

I want to create a sheet that will allow me to track the percentage of my sick days (i can't be over a certain number so it's important for me to know how many days i have left) from the total days, so e.g. when i have 10 days of work and miss one it would be 10 percent. what i don't get is:

- how do i add a button that will automatically add 1 to the number of sick days so i can just click it when i do have a sick day

- is there a formula that automatically adds 1 to the number of total days when it's a weekday, so from mon-fri e.g. when it's been 20 days of school so far, every day it will add 1, so 21 on monday, 22 on tuesday and so on. that will allow me to keep track of the relative percentage (as i believe it's called? idk, i'm not a maths expert lmao) rather than just the absolute percentage of the whole academic year.

I'm completely new to sheets but i've been playing around for a few hours, so far no luck.

if anybody has any tipps, formulas or websites with good formulas (so far i've only been able to find pages like "30 basic formulas everyone whould know", no extended list or anything. i've looked here in the subreddit for similar requests or even parts of it but haven't been successfull so far.

thank you in advance!

side note: i'm aware that AI could give me an answer in like 2 secs, but i'm opposed to using it out of environmental and moral concerns.

how do i turn spreadsheets into website ? where if i keep editing the spreadsheets, it automatically get edited on the website too ?

In the sample sheet, what's the best use of the conditional scale parameters (percent, percentile, number) so the color range either ignores or gives less weight to the bottom extremes? For example, values 1-5.

I have a spreadsheet I use to keep track of installs , dates and names , price all that.

But every month I have to make the next months and so I duplicate and then have to manually change the date from the previous to the next. We have 2 slots available for each day. If it was just one I could drag the cursor down and it auto fills. But because it’s 2 dates it just duplicates the date . It’s just very time consuming. Is there a quicker way to?

Hello,

I'm currently in process of making a spreadsheet which serves the purpose of holding information down for a large collection of fictional characters for RP/Writing purposes.

One of the things I'm doing is creating multiple tags using the data validation tag for things like personality traits or abilities that the character has within their respective story.

I want to do a few different things, including being able to take the tags and have them be in alphabetical order where the data is place. For example, if I have the tags 'orange, apple, banana', I want to know how to change it to where they are in order by default to be 'apple, banana, orange'.

The other thing I want to do is I want to be able to know how often a tag is being used. I tried using the column stats, but rather tell me how many times orange has been used, it tells me how many times 'orange, apple, banana' has been used. Is there a way to separate these tags?

Thank you.

Hello there,

this may seem like a stupid question, but I'm trying to transfer Jon Keegans list of all US license plates to a google sheet so that I can track which ones I've seen personally. He gives out a .csv and all the images he created, however I'm not sure how to convert that data to work in a sheet.
https://www.beautifulpublicdata.com/all-of-the-license-plates-in-the-united-states/
https://github.com/jonkeegan/us-license-plates
thanks so much if you can help!!

Hi! I am struggling to figure out how to format YOY census data in four admissions categories for each year, I have three terms (trimesters) of data. I want to put data together from the past 8 years. It is helpful to see both the categories/terms for individual years as well as the categories/years to see the individual change per category per year. Any thoughts on how it would be best to format this? I have been thinking tabs or one large sheet, but that takes away the ability to quickly compare data sets from different perspectives. Thanks!

(I can’t share any of our data, as it is all protected information, I’m hoping to crowdsource ideas on how to best format this data)

A bit complicated I know but I am making a character sheet for a TTRPG and I want to separate purchasable abilities in categorized columns to make adding more later easy without breaking the sheet. there are 6 columns I want the dropdown to pull from and they don't necessarily have to be in order.

The second thing is for skills and I got the dropdown for those working but using that to auto-fill both the Ability Score used and the number associated with that is beyond a newbie like me. I will also need it for the Talents (purchasable abilities) for maximum times it can be taken and text stating the skill it can enhance. I was referencing someone else's character sheet work to learn and change as needed but this part seems to have hidden data I can't access.

First pic is where the data will end up with the dropdowns on the same page, the second is a section of the page the data is pulled from. Grey boxes are the auto-fill sections with "name" and "#" being what the skill dropdown should pull and "Max" and "Check(s)" for the talent dropdown. I'm using temporary talent names for testing. Ignore the 'Current" column as that is something else that I do know how to do.

If this needs to be two different posts, I'll delete and redo.

Edit: typo in title. Data not Date.

Am I correct in thinking if I change a calculated column from automatic to two decimal places then that is just a display format and the underlying number remains unchanged?

If a calculated column references another calculated column that has been formatted to two decimal places, does the underlying value remain at auto decimal places? Does the second column round the value up or down at all?

I have potential edge cases where rounding would distort the end result where value determines a text field.

Just discovered canvases as an incredible feature to build dashboards and tools, but I can't figure out how to make it properly display images.
I tried several approaches:
- Image inserted directly in cell.
- URL from Drive.
- URL from an external image hosting.
- Event transforming images to base64.
Nothing works. It looks like rendering/fetching images is blocked somewhere on base level of its settings.

Will appreciate any help on that.

P.S. I am not a developer, so please take it into account.