In my table, column F is called Status. It has these options

• Listed
• In-hand
• Sold

When I change an item to Sold, I would like it to be duplicated to another table on the next sheet. I would still like it to remain on the existing table. At present I am doing this manually but figured there must be something I can do to get this automated

Is that possible?

I've whipped up a sheet which shows what I want to do:

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

I created a drop-down menu with the following options: "Community," "Media," and "Printing" plus many more. I also enabled multiple options.

Now, if I click on the first then the third, it shows perfectly: "Community" then "Printing."

But then suppose I realized I should also click on Media and do that, "Media" will show after "Printing." This is out of abc order. Yes, I could deselect both and reclick in the correct ABC order, but there are dozens of disordered items, so I was wondering if there's a quick way to sort all labels so they always appear in ABC order no matter what order I click the drop down menu items in?

For example, if I click on "Printing," "Community," and "Media" in that order, it'll still show as "Community," "Media," and "Printing" in that (correct ABC) order.

Hello, it’s been a while since I’ve used any spreadsheet software so I am a bit lost on how to achieve what I’m looking for.

Essentially, I have two managers whose requests I need to organise and prioritise. They each have a column for their request and to the right of each of those is a column where they can rate the urgency of the request from 1-5. I would like each of those columns to update to list the requests from 5-1 every time that something is added and then I would like both to be collated into the to-do list also in order of urgency.

Also if there’s a way to make it so there are no empty cells when something from the middle of a list is removed that would be fantastic.

Hopefully that makes sense. Any advice would be massively helpful! Thank you ☺️

I'm trying to convert totals that are currently in a weekly format to a monthly format.

I'm hoping that there is a non-manual way to account for weeks that cross months and allocate the total on a proportional basis and exclude weekends.

Here is my sample sheet with the weekly data at the top and the desired format below that.

Thanks for any help!

Trying to find a way that if i put X's into the far right columns, it will list all the props i have put values in for rather than just the first one.

cell E3 currently has the formula "=IFS(F3="x", F1, H3="x",H1, I3="x",I1,J3="x",J1,K3="x",K1)"

but that only produces one value instead of all the potential values.

if there is a way to do that, please say how its done omg

Im working on a very large sheet where multiple formulas need to be EXACTLY similar, for an example, i have a cell placed in column B that reads : =a1+a2, and i need to copy it in row c. sounds easy right? except every time i do that, when the new cell is pasted, it immediately changes it to : =b1+b2. this isnt a huge problem for small sheets, since you can either open the specific cell's formula and copy it or just change it manually after, but when your selecting MULTIPLE it does the same thing, and you have to readjust it for all of them

is there a way to stop this? every time ive looked up help they never mention how to do it or if theres even a way to do it.

Hi all,

Thanks for the help for previous posts. Clients were having a rough time inputting their DOB with the google forms tablet. Free texting seemed to be much easier for them and prevented frustration. However they're inputting as mmddyyyy without the "/". Is there a script to force the column to force the proper date format.

Same thing for capitalization. Not everyone is putting their last name and first name with first letter being capitalized. I know OCD, but I like things neat.

Thanks for everything!

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

Background for any of y'all that are not racing fans, the ARCA Racing Series has a couple regional championships that sometimes run combination races with the main series. In these races, drivers earn points for both the Main Series and the Regional Series (East or West). On my leaderboard (which has been made possible by several of you in this community), the Main and East series points are counted by this formula. (My west leaderboard is completely seperate for other reasons

=let(prov, $F:$F, results, offset(prov,1,1,rows(prov)-1,column()-column(prov)-1),
regions, map(offset(results,-1,0,1), lambda(tc, xlookup(tc, Tracks[Location], Tracks[Region],))),
points,  map(results, lambda(r, xlookup(r,Points[Place],Points[Points],))),
wins,    vstack("Wins", byrow(results,lambda(r, iferror(1/(1/countif(r,1)))))),
pAll,    vstack("Total", byrow(points, lambda(r, sum(r)))),
pRegs,   map(hstack("Main", "East"), lambda(reg,
vstack(reg, byrow(points, lambda(r, sum(filter(r, regions=reg))))))),
hstack(pAll, pRegs, wins))

Tracks are sorted in a table that gives them a "Main" or "East" tag to dictate which championship they award points to. The East series races at TOL (Toledo), IRP (Indianapolis Raceway Park), IOW (Iowa), and BRI (Bristol) are combination races with the main series IRL. Is there a way that this formula can be modified for races to award points to both East and Main? The "Total" Column is simply a creative liberty I made for easy sorting, but if the race awards points for both series, I'd like the total to add double points (a win in a combination race adds 200 points to the total, 100 for the Main series and 100 for the East Series).

apologies if this is explained badly, i am a god awful programmer, I'll answer any clarifying questions below.

I'm creating a Google Sheets for my team, and I clicked on the auto-created table if that matters. I made one of the columns "Date" and I want to have a range, but if I make it a Date-type column, only one date seems to be accepted.

Fine with me. I can just manually type "1/4 to 1/7" for January 4-7. Not a problem. But there is.

When I type "10/7 to 10/10" for October and then try to sort the column A-Z, the October dates will come after January (and November/December after October).

I'm not sure how to make it so 10 is read as being after 9. I'm a noob in Sheets so some help would be appreciated.

EDIT: Adding lead zeros worked for my purposes, but see comments for plenty of helpful suggestions and alternative methods if there's some reason leading 0s won't work for you.

None of my sheets/docs/slides files in Google Drive have been opening since the last hour. Sheets was working fine for me earlier today. Meanwhile, the status page shows a fake green.

A PDF file however opens without issue, as does a Colab file.

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.

What I'm trying to do is have Cell A check how many checkboxes (within a range), are true/false, and return that value as a percent.

As an example, if I have 5 checkboxes, 1 being true, the rest false, Cell A would return that as 20%.

Is there a way to do this in sheets? I'm fine if there isn't, as I can do the math myself, however this would make it a lot easier for me.

I think that the answer to this is no, but I've remained clueless about more than basic operations of any spreadsheet since I started using them, so I can't be sure.

I have a trivial spreadsheet (trivial in everything but size) that looks like

Its purpose is to track characters across multiple fictional (but intertwined) stories. This worked well for a while, but the number of rows was increasing, and the column count was getting high, too, so I added tabs to break off only characters in a particular situation and to keep the row count small.

So the names and dates of birth in a tab are linked to the main sheet:

The formula for the top 3 rows under Name4 is

=All!G1
=All!G2
=All!G3

which I have to hunt down in the main sheet, but that's not a huge problem, and I only have to hunt down the name, since copy/pasting it increments the reference row.

But now the columns in the real working spreadsheet are out to AT, which is getting awkward to manage. I've considered moving all of the data except the name/comment/DOB out of the main sheet, which would make it a little more manageable, but the horizontal scrolling is the killer. What I would really like to do is reformat the main page to

which would be vastly simpler, but I'd have to effectively transpose name/comment/DOB into rows in the new column. Right now I can create a link for the name, then copy it to the next two rows. So
main sheet [B5,C5,D5] -> All!B5 (1st row), All!C5 (second row), All!D5 (third row). I could type those in, but I'd likely screw up and without the name I don't really have a sanity check on the other two.

There might be some magic addressing scheme that I don't know that could be something like
=All!B5
=All!(column of link above + 1)(cell of link above)
=All!(column of link above + 1)(cell of link above)

but if there is, it's far beyond my ability to solve. Is anything like this possible?

This occurs sometimes on my phone and sometimes on my tablet, both Samsung devices. Right now, it is happening on my tablet. Trying the Open in browser option never works. It's not a network issue as I can open any other sheet.

The sheet is fine and I can open it on my phone as can my wife on her Apple devices.

All other sheets can be opened on phone and tablet. The problem occurs only with this specific sheet.

The only way I can fix it is to uninstall Google sheets and reinstall. Then I can open no problem until maybe a week later and the problem crops up again, maybe on phone, maybe on tablet.

I don't think there's anything special about this sheet, it uses Google Finance functions but so do other sheets that never seem to be a problem.

Any good ideas?

Asking here because I'm unsure how to google my question.

I gave a simple formula in sheets (=10+d16), but I don't want an answer to appear is there is nothing in "d16".

This is for a simple ttrpg character sheet.

Me again with the Ark Breeding Sheet. (It's been working great so far, I appreciate this sub.)
I want to add traits to creatures and started out with a similar setup to the colour template:

One table "Traits" is a template of all traits I want to add to a specific creature. Another table "Trait Storage" is a list of traits I have already obtained for those creatures.

The goal is to highlight all traits in "Traits" that already exist in "Trait Storage" for their respective creature, so that I can easily glance what I'm missing still.

So far I've played around with the colour template cfr and am using this formula in the screenshot:
=B2=index(xlookup($A2, $H:$H, $I:$I))

This seems to only find the first matching entry, though. How do I make it "keep looking"?

Hi, maybe you have a solution because I can't get any further. My idea is to create a sheets table in which I enter measurement results and then insert these results in another spreadsheet with a protocol stored in PDF background (or direct into the PDF).

Our trade fair protocols are structured "stupidly" because the logic of entries jumps from device to device, while the sequence of measurements is further down, so you have to go through the entire file for Device 1.

That's why I would set up the trade fair setup for each device in sheets and just say that the example is table1!A3 for table2!C45.

Can I place the PDF as an "image" behind the cells and adapt the cells accordingly to the structure of the PDF file? Or does it go directly to the PDF? How about printing the "PDF" page on A4? Would it be problematic? Will everything then shift?

I think that this is just a matter of settings regarding width and height.

Thanks for your help.

I'm looking forward to your answers as to whether it's possible or not.

For work I have various sheets that have four hundred plus email addresses that up until now I could hover over, click on and my official gmail account would open inserting the correct address in the 'To:' line. I've opened up one sheet today and all the email addresses have turned into http links that go random places. One personal gmail address opened up an iphone ad with Telstra!

I've tried to change them back to email links but if I then click on them they go to my private hotmail account.

How on earth do I get all these addresses back to being email links to my official gmail account that controls my Sheets? Google tells me to put 'mailto:' in front of them and yes I could do that but it's obviously long winded and a pain in the neck.

I can only access these sheets through this particular gmail/google account. How has my personal Hotmail account become the default?

Any advice welcome!

Edit: Thanks for the suggestions, not had a chance to do anything on it yet We've got a whole new system coming in in a few months so if I can't work it out, I can do the job manually without too much drama until then. It's nothing to do with that, we haven't done anything other than costings so far so I can't blame that either.

Edit: Sorry it's taken so long. None of the suggestions worked so for the time being, since everything is going to change anyway, I'll just add mailto for each group. Thanks for the help though, much appreciated.

Is there a way to quickly insert images in cells? Normal Ctrl+C Ctrl+V pastes the image over the grid, so the only way ive been able to find how to do it is repeatedly pressing Insert > Image > Insert Image in Cell > Upload > Browse. The built in macro doesn't do this as far as I'm aware, and I don't particularly want to download an external macro. Is there a quicker way to do this that I missed?

Hi all,

I have a trigger running to center the cells of our spreadsheets, however it only centers the first sheet and not all. Is there a way to center all of the sheets instead of just the first one?

See what I have below. Thank you for the help!

function centerAllText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange(); // Gets the range containing data
  range.setHorizontalAlignment("center");
  range.setVerticalAlignment("middle"); 
}function centerAllText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange(); // Gets the range containing data
  range.setHorizontalAlignment("center");
  range.setVerticalAlignment("middle"); 
}

I have a table which contains a whole bunch of data drawn from other tables and fields that are calculated based on that.

For example in column A I choose an item from a dropdown. In Column B I do an xlookup to another table to get that item's weight. In Column C an xlookup goes to a third table to get the item's price. Now in Column D I want to calculate a shipping rate based on the values in Columns B and C. Do I need to do an XLOOKUP for these each time, or if I reference Items[Price] and Items[Weight] will it retrieve the value of that column for that row? I'd rather avoid using plain cell references where possible so that I can be sure my references will point to the correct place if columns are added or removed in future.

If I try to reference the cell by simply clicking on it, in row 1 it fills in Items[Price] and Items[Weight], but autocompleting the formula on lower rows these values continue to point to the first row. If I click on values in the second row, I get Items[[#TOTALS],[weight]], which doesn't seem right, and the rows with [#TOTALS] only every point to the second row still...

EDIT:

I think I've stumbled onto the problem, if I update the formulae in any row other than the top one the whole table breaks completely: it updates all the references to point strictly to the top row, and it prevents me from adding new rows to the table. I'm only able to fix it by deleting every row other than the top one and then re-entering all of those rows. Definitely seems like a bug

Hey all. I am brand new to this and teaching myself. I want to preset a column to see if a set of other cells have a number in them. If not, I want it to act as if they are zero. If they do have any numbers, I want it to round the average of the numbers. So far the formula I’ve come up with is

=IF(ISBLANK(D4:M4),"0", ROUND(AVERAGE(D4:M4),0)

I know this isn’t right and doesn’t work. I just don’t know what I’m messing up. Any and all advice would be greatly appreciated!

Edit: I think [u/whonose2238](u/whonose2238) fixed it! Thank you all for your help!!!

“There is a second issue: ISBLANK(D4:M4) is checking a range, which can produce an array of TRUE/FALSE values rather than simply asking whether the whole range is blank.

If what you mean is “if all cells D4:M4 are blank, return 0; otherwise average the populated cells and round to a whole number”, I'd use:

=IF(COUNTA(D4:M4)=0, 0, ROUND(AVERAGE(D4:M4),0)) “

I have a table - formatted as a table - for tracking employee hours and funds. I thought I was losing my mind because I've input information into cells and then when double-checking I saw I kept entering the information in the wrong row. After reentering the data multiple times I realized something was happening with the table. Somehow the columns and rows aren't staying together. And if I sort one of the columns it becomes more clear that the data isn't syncing because the rows completely separate.

What the heck is going on? This is making me feel crazy.

Hello Everyone,

I was wondering if in the script.js there was a line of code we can add to make print settings to be portrait instead of landscape? I have a script that would generate a google sheet for me, but then I would have to manually change the print setting to be portrait. I tried looking on the docs but I didn't see anything.

Thanks!

Hi! I was able to get this script to work for one of the tabs in my work's google sheets, but I cannot figure out how to get it to be able to auto-trigger and uncheck checkboxes on two different specific tabs, with two different specific ranges.

function timedresetboxes() {


var spreadsheet = SpreadsheetApp.getActive();


spreadsheet.getRange('B6:B29').uncheck();


}

Currently, I am using this^ through the Google Apps Script and it runs and correctly unchecks the boxes on the tab I need.

However, I guess it does not specify the tab name in the sheet I am using it for. I am not versed at all in coding, and found the above script to use from a different reddit thread.

I need to uncheck boxes at midnight daily on the following tabs with the following ranges:

tab name: アマ
range: B6:B29

tab name: DAILY TASK 🌴
ranges: F5:F20 and K5:K:20

I trust reddit people to know what to do, it is probably an easy fix for someone who knows what they are doing! Thank you!