Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

Filter by
Sorted by
Tagged with
0
votes
1answer
11 views

Find whitespace and replace with string from formula in Google Sheet

I have list of cities and I want to create URL for each of them in Google Sheet. Scenario is most of the part of the URL is same only at the end city name is different so I created a formula =...
14
votes
3answers
17k views

Google Sheets Increase month by 1 but leave everything else the same

I am using Google Sheets and have 700+ rows that have an assortment of dates but the month is the same on all of them (monthly billing cycle) each month I am having to manually go through each line ...
0
votes
2answers
25 views

How to correct this formula with If function?

I have a data filled from Column C9 to column I39 AND I Auto-Add text in Column J using this formula: =ArrayFormula(IF(I9:I39="Excellent","Excellent",IF(I9:I39="Good",&...
0
votes
1answer
11 views

How can I use a Drop Down List and skip data validation in Google Sheets?

I want to show a Drop Down List in a cell in Google Sheets, where user can choose a list of words. But I want to let to user to input any number too. Since the only way I know to show off a drop down ...
0
votes
1answer
32 views

Assigning a value to a name from a list

I have items that I'm selling, and I've named these codes with the item I'm selling. I want to give these codes the price they should display. I've already got a formula to show the product of the ...
0
votes
0answers
11 views

add link as function input for google spread sheet function [closed]

I have found an online google sheet and I want to add more "automatic" inputs to it but I don't know how. The owner says it works with new URLs copied into the sheet but where should I copy ...
0
votes
1answer
33 views

Query or filter the data when using date time format

Example sheet. Sheet2 In cell A2 I have: =QUERY(Sheet1!A1:Q, "Select * where B >= datetime '"&TEXT($A$1,"MM-DD-YYYY HH:mm:ss")&"'") In cell A1 I have: 6/17/...
1
vote
0answers
12 views

How to add date criteria to query function? [duplicate]

I am using query function to copy all data from sheet1!A2:B10 to Sheet2 if B column is equal to specific data/time. Lets say A1 = 5 April 2021 A2:A10 names B2:B10 has Timestamp And I used this formula ...
0
votes
1answer
849 views

Google Sheets - search multiple columns for specific text

Info added after as response to first comment: I just realized that the IF(PAGE1!C5="OPTION1" probably wont work, probably because I haven't described the wish good enough. I will describe with this ...
0
votes
1answer
83 views

AGGREGATE function of EXCEL into Google Sheets

I have perfect working formula for EXCEL including the AGGREGATE element, but this doesn't work at Google Sheets obviously. I am using German MS Office, but I translate it into English for better ...
0
votes
1answer
12 views

Run a Google Sheets script on all tabs except one

Thanks in advance for the help. I am currently running a Google Sheets script which applies to one tab called "Tab 1". It copies a range across to the next free column as follows: function ...
1
vote
3answers
101 views

Google Sheets Checkbox vs. Tickbox

Sometimes, I've seen Checkbox but I've also seen Tick Box. Does anyone know what's going on?
0
votes
1answer
24 views

Google Sheet Search Button

Sorry beginner and just starting to try Apps Script just for a few weeks. I'm create a form for my Inventory Tracker with few button 'Search', 'Clear Form', 'Save Data', 'Update Data' and 'Delete Data'...
2
votes
1answer
52 views

Logging a value that changes once a day in Google Sheets [duplicate]

In my Google Sheet cell D12 gets the difference of two numbers with =D9 - D4. How do I store the value in cell D12 to a log in another sheet on a daily basis? I would like each row in the log sheet to ...
1
vote
1answer
9k views

Using AGGREGATE formula on Google Sheets - Finding the correct result based on 3 columns of criteria

It took me a long time to get this working on Excel, and just as I managed to do it, my company changed over to Google Sheets! I work for a packaging company, and we sell hundreds of different boxes ...
3
votes
1answer
607 views

Time tracking with Google Spreadsheets

I'm using Tasker and am trying to set up a profile that would track how many hours I spend at work. I've managed to set up the tracking part, which inserts the data in a Google Spreadsheet like this: ...
2
votes
1answer
28 views

Plot count hours, each date separately, from non-standard datetime values, in Google Sheets

The data in the 'Input' sheet starts in cell A1 and looks like this: 2021 04 06 7 43 2021 04 06 11 15 2021 05 01 10 11 2021 05 01 13 18 These are datetime values so 2021 05 01 13 18 means 2021 May ...
0
votes
1answer
966 views

How to not count cell if cell contains sub-string

I have the following formula to count cells within a range B10:B1095 which do not contain certain strings =COUNTIFS( B10:B1095, "<>", B10:B1095, "<>Manual", B10:B1095, "<>TODO"...
0
votes
0answers
16 views

Generate a line chart with each player's running score (competition tracker) [closed]

I asked this question on https://www.reddit.com/r/excel/comments/mikwvz/competition_tracker_create_publicly_accessible/?utm_source=share&utm_medium=web2x&context=3, but didn't get an ...
1
vote
1answer
17 views

Split text in Google Sheets with a delimiter which contains spaces

I have a column (A) in Google Sheets where each row contains the following string: "TEXT on MORE_TEXT" or, for more specific example "occurred on December 17, 2020". The "...
1
vote
1answer
57 views

Count of each number where the cell to the right is 1 number higher in an 8 column, 25 row grid

Table: 1 2 3 5 6 8 9 11 2 3 6 7 9 10 11 12 1 2 4 5 6 9 11 14 Output: 1 2: 2 2 3: 2 3 4: 0 4 5: 1 5 6: 2 6 7: 1 7 8: 0 8 9: 1 9 10: 1 10 11: 1 11 11: 1 12 13: 0 I've tried countif w/ ...
0
votes
1answer
22 views

Is there away on google sheets to paste text and automaticly create new rows with every line break or etc?

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect? I will paste a lot of text by this format in the example below (700 lines in total): ...
11
votes
1answer
13k views

Export all sheets in a spreadsheet to csv in Google Apps

Is there a way to export all sheets in a google spreadsheet to csv as individual files ?
4
votes
2answers
1k views

Jump to bottom of spreadsheet, blank (non-formula) row

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in ...
3
votes
1answer
125 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
5
votes
2answers
4k views

Copy range from specific values to top of another sheet

I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2). Sheet1: The data between "Start" to "End" can be any number of rows. This is my current code. It ...
1
vote
1answer
31 views

Create a Unique Order Number From Existing column of values

I need to create a unique ID from an existing list of order numbers. Pls see the image below: What I want is for the existing duplicate order numbers to be made unique by adding an extra digit to the ...
0
votes
1answer
102 views

Display a list of names if today's date equals a date in any adjacent cell on the same row

My spreadsheet has "variant" names in Column B, and relevant dates in adjacent columns C-H on the same row. I have included a screenshot below I want to create formula that will display a ...
5
votes
2answers
412 views

Odd Google Apps Script behavior

I have a Google Apps Script project that I have used for the past few months (successfully) and everything has worked just fine. A few weeks ago, I started getting the "Unable to load files" error ...
0
votes
1answer
68 views

Can I populate Google Sheets cells adjacent to a target cell?

I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
13
votes
3answers
5k views

Statically positioned chart (ignore scroll)

I have a Google Sheets document with four columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen. Is there a way to make it ...
0
votes
2answers
587 views

Conditionally formatting duplicate values in Google sheets with exclusion criteria?

I'm currently working in a Google sheet where I use the following formula to catch duplicate values with conditional formatting: =countif($B:$B,B2)>1 While this works on its own, I find that it ...
0
votes
1answer
171 views

Utilize SI units in Google Sheets

Seven years ago this was asked, are there any new developments with regard to formatting with SI units? Normally, I will adjust the format to be all milli or micro simply by division, however my ...
0
votes
1answer
52 views

Conditional formatting with multiple names in list

Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like: H3 = John, Steve, Bob, Dan Is there a way to use conditional ...
1
vote
1answer
74 views

Is there a way to group rows by a term and insert this term as a header in Google Sheets

I have a list of books on Google Sheets that will be turned into a paged layout on InDesign. Each book has two additional information besides its title (year and theme). Like this But I need this ...
4
votes
2answers
2k views

Batch Hiding Function in Google Script

I wrote a function that iterates through every non-blank row in the sheet, checks the value of a certain cell, and then if that cell matches my criteria its row number is added to an array. I then ...
0
votes
1answer
30 views

How can I color the target of a SUM formula in Google Sheets

If I double click on a formula with a cell range, Google Sheets will outline the target area: Is there a way to get similar highlighting at all times, updated as the range or ranges in the formulas ...
0
votes
0answers
20 views

Google Sheets formatting

I have a workbook in Google Sheets and it is for a schedule for my company so I have a few sheets in my workbook that have the days of certain weeks and then one more tab that has inventory ...
0
votes
1answer
28 views

Concat columns from query result in Google Sheets

I have a sheet that has a column with labels in a single cell. A cell can contain 1 or more labels, each separated by a comma. +-----------+ | Labels | +-----------+ | Foo,Bar | | Bar,Baz | | ...
0
votes
1answer
18 views

Converting a googlefinance() market cap to another currency in Google Sheets

I can get the market capitalization of Bank of Montreal with =googlefinance("TSE:BMO", "marketcap"). The result is in US dollars. How do I get it in Canadian dollars?
0
votes
2answers
34 views

How to normalize different date formats when importing in google sheets

Background The problem I'm solving is mentioned here in detail. Another glitch is that the spreadsheet I'm reading from has random changes to the date format like so: 10/3/2021 11/3/2021 12/3/2021 ...
2
votes
1answer
604 views

Check whether a value exists in either cell in a pair of rows for a whole column

I have a spreadsheet that operates as a roster and I want to work out coverage. So each column represents a shift and each pair of rows represents a day because each day has two slots available. To ...
0
votes
0answers
6 views

Google Sheets and conditional formatting [duplicate]

I have a workbook in Google Sheets and it is for a schedule for my company so I have a few sheets in my workbook that have the days of certain weeks and then one more tab that has inventory ...
0
votes
1answer
860 views

Generate “cards” with info in Google Sheets

I have a sheet for an event and I need to generate identification cards like in the image for the pieces on the contest, around 1000 pieces, so no way to manually do it. I did a similar thing years ...
0
votes
1answer
25 views

Google sheet - highlihghting cell if in the same row other cell not empty

I'm trying to make a worksheet where I log shift hours. I made columns with dates and to each date a row of hours I want the date text to be green if in the same row a cell is not empty.. The code I ...
1
vote
0answers
12 views

Google Sheets - Create Searchable FedEx Tracking from Barcode Scan

We use about 100 Google Sheets to track incoming packages for various companies. When we receive a UPS package, we scan the barcode, assign it to a sheet, and when we search the tracking number in ...
0
votes
1answer
51 views

Change cell colour based on another cell containing text

I am new to Google Sheets and trying to create a custom formula in conditional formatting. Looking to see if someone would be kind enough to help. The picture below is a sample size of two list of ...
2
votes
1answer
1k views

Email notification if cell value goes below set amount

I'm having trouble finding a Google sheet script that does what I need so I figured I would ask if someone could help me. Basically I have a simple sheet setup to track inventory of one item. There is ...
0
votes
1answer
20 views

Keep formulas working when copying multiple sheets (tabs) from one spreadsheet file to another in Google Sheets

I have a complex spreadsheet and need to copy some of the tabs to another spreadsheet file. Formulas no longer work when I am done copying. How can I fix them?
1
vote
2answers
214 views

Array formula with index match to return array of matches

I've been working at this for a bit. I have tried many times different formula combos, using VLOOKUP to using INDEX & MATCH, even with an ARRAYFORMULA. What I'm attempting to do is match a ...

1
3 4
5
6 7
137