Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. 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 chances to get a satisfactory answer.

Filter by
Sorted by
Tagged with
4
votes
2answers
76 views

How to get a new date when calculating from today plus an integer

I use Google Sheets to calculate my word count & rate for daily writing sessions. I'm trying to come up with a formula that will return the estimated date of hitting a goal, based on my current ...
2
votes
1answer
25 views

Query a column based on current date

I'm trying to query a column based on the current day. Examples: If today is 1st day of the month I need to: =QUERY(table,"SELECT A WHERE B CONTAINS 'this text'") If today is 12th day of the month ...
0
votes
1answer
18 views

Indirect formula to add table field to vlookup formula

So I have to get a price based 3 variables Diamond size Diamond quality Diamond Shape So I made tables depending on shapes This formula is working Diamond size = H11 = 1 Diamond quality = $K$3 =...
0
votes
0answers
10 views

Fill a blank cell with the value next to it

As you can see in the image above I have a spreadsheet and got some cells there that aren't filled. I want to fill these cells with the value from the previous date (for example F3 with the value of ...
1
vote
1answer
16 views

Simplify incoming Dates and time to Month and Year in Google Sheets

I currently have payments going into a Google Sheet in the current format: Date | Company | Location | Category | Paid | | ...
-1
votes
0answers
12 views

How to make API data persistent inside Google Sheets?

I am tracking the price of a cryptocurrency in Google Sheets. I use the formula below. The charts I draw depending on this data loses their chart data until all the historical prices can be reloaded ...
0
votes
0answers
3 views

App script to update the data changes to multiple sheets

I made a script to combine data from Sheet1 and Sheet2 into Sheet "Master". If there is a data change in the "Master" sheet, I write the script to update the data changes to each sheet. Source //...
0
votes
1answer
7 views

How to clear a cell if there is no data in it

How to clear a cell if there is no data in it? =ArrayFormula(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"))
0
votes
1answer
748 views

Google Spreadsheet Stock Sparkline Trend Conditional Formatting

I am trying to do a Sparkline whose color changes based on the trend and for some reason the if clause which gets its input from google finance isn't working =sparkline(query(googlefinance($C5, "...
1
vote
2answers
7k views

Selecting Multiple Sheets Simultaneously

I'd like to copy multiple sheets to another spreadsheet, it doesn't seem selecting multiple sheets simultaneously is a feature. Is there a workaround, add on, perhaps an available script?
0
votes
1answer
25 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 ...
4
votes
3answers
22k views

How do I share a Google Sheet with 'read only' permissions?

Given some Google Sheet I'm aware of the Share button in the top right corner. However, the default settings allow anyone with the share url to edit the sheet. Is it possible to set permissions so ...
0
votes
1answer
81 views

Formula for adding sum total from multiple sheets for a specific name

I am stuck in terms of how to set a formula to get the sum total for a specific "name" from multiple sheets.
1
vote
2answers
42 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
0answers
17 views

SUM formula that always uses the cell on top in Google Sheets?

I got a SUM formula that adds up all values in a column: It's the bolded one that I'm referring to. Whenever I add a new row, I have to manually open the summation formula and update the range. Is ...
0
votes
0answers
8 views

Shortcut to go to the referenced cell in a formula in Google Sheets?

Say I have two sheets in a Google Sheets file, Foo and Bar. In Foo, I have a formula in cell that references a cell in Bar. How can I switch the view to the referenced cell by using a shortcut? I ...
0
votes
1answer
733 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"...
-1
votes
1answer
44 views

Copy cell info on account of cell matching specific te

Edited as response to comment: I had to copy data into a new sheet, which is why it doesnt entirely match your answer. Here is where Im at now: attached screendump is new. Hope you can help with ...
8
votes
6answers
22k views

How to add N more columns?

As of this writing, the default number of columns in Google Sheets is 29 columns. I need more than 29 columns. I would like to add N columns where N is something like 10 to 30 columns. My request ...
2
votes
2answers
28 views

Copy entire rows from one sheet to another if certain cell in first sheet does NOT contain certain text value

I have two sheets. The first one has incoming data that dynamically updates. The fields are Name, Last Name, Email. I want to duplicate the rows in a second sheet but only if the email field in sheet ...
3
votes
1answer
123 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 ...
2
votes
1answer
3k views

Conditional Formatting Script to Change Font Size in Google Sheet

In Google Sheets I'm trying to find a way to conditionally format the font size of the cell for Column A in a row, based on text found within the cell of Column B in the same row. For example, if ...
2
votes
2answers
17k views

Trying to subtract X days from a defined date in Google Sheets

I'm trying to figure out "how to" subtract days from a date in Google Sheets. EXAMPLE Col A (original date) Col D (date - 5 days) 08-08-16 08-03-16 A1 will display the ...
3
votes
2answers
4k views

How can I set a monospace font in a Google Sheets column?

How can I apply a monospace font to a Google Spreadsheet cell or column? This capability is easy in Excel, Gnumeric, Libreoffice or other desktop spreadsheeets. As of this writing, there's no font ...
0
votes
1answer
47 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 ...
0
votes
0answers
19 views

Google Sheets Increment with INDEX

I have the formula: =IF(INDEX(INDIRECT("Rota!" & $B$1):Rota!$Z$1000, 1, 2) = "", "-", INDEX(INDIRECT("Rota!" & $B$1):Rota!$Z$1000, 1, 2)) I would like to increment it by 1 every time I drag ...
1
vote
1answer
1k views

Google apps script to check availability

I'm using google form+calendar to create a reservation system for my makers lab. The user fills out the form and an event is created on the calendar so the TA knows when to goto the lab to help the ...
0
votes
1answer
10 views

How can I have conditional formatting with lower priority than manual formatting

I know how to do the formatting that I want. The document should have conditional formatting. However When I added it. It covered the manual formatting. It there a way to say do this conditional ...
0
votes
2answers
438 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 ...
-2
votes
0answers
15 views

Google Sheet Highlight differing words when comparing two cells (both contain text words)

How to write google script so that we can Highlight differing words when comparing two cells (both contain text word strings)? Basically script version of the VBA code here https://superuser.com/...
1
vote
1answer
10 views

In google sheets - how return *1.54M* as $1,540,000 with the same formula that I would return $1.35B* as $1,350,000,000?

I am building a stock model - where I use =importhtml() to give me financial data pulled from a website on certain stocks. It is returning it correctly, but there are asterisks surrounding all the ...
0
votes
1answer
34 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 ...
38
votes
5answers
59k views

Multi user filter issue in Google Spreadsheets

When two or more users view the same Google Spreadsheet and one of them filters the data by a column, that filter is immediately applied to all other users as well. Is there a way to avoid this and ...
0
votes
2answers
608 views

How can you use a function and pass it a column inside a Google Sheets Query?

On Sheet 1, I have data CATEGORY | PATIENT | DATE | AMOUNT Vision | Joe | 1/1/2016 | $100 Vision | Joe | 1/2/2016 | $100 Vision | Joe | 1/3/2016 | $...
-1
votes
0answers
10 views

Google Sheets | How to check how two or three values relate over time

Do you think is possible to build a sheet with 3 variables(A,B,C each in a column) and say a set of 1000 values(1000 rows) And have a way to produce a report on how they relate over time ? I'm ...
0
votes
0answers
7 views

Trying to autofill, but don't want the error in the cells for dates I don't have data for yet

So I created a function. For dates I have data for, it works just fine. For dates yet to come, I just have a whole bunch of cells with error messages. I want it to fill in automatically as I put data ...
1
vote
1answer
69 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 ...
0
votes
1answer
25 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
6 views

Google sheet query, number of columns in table affect speed

I have a large Google sheets tab. It has about 1500 rows and the columns extend out to AR. It was querying really slow until I got rid of a few columns and shrunk the columns to AB. The pick in ...
1
vote
1answer
894 views

Auto import CSV into existing Google Sheet

I understand how to upload and convert CSV files to Google Sheets -- but how can I set up an automatic upload of all new data from a CSV file into an existing sheet? I have a CSV file that lives on ...
0
votes
1answer
11 views

How can I simplify a formula for Google Sheets?

How can I make this formula simpler: =SUM('DAY1'!P39,'DAY2'!P39,'DAY3'!P39,'DAY4'!P39,'DAY5'!P39,'DAY6'!P39,'DAY7'!P39,'DAY8'!P39,'DAY9'!P39,'DAY10'!P39,'DAY11'!P39,'DAY12'!P39,'DAY13'!P39,'DAY14'!...
1
vote
0answers
28 views

Input Data from Individual Sheet (based off cell value/name)

I'm wondering if it's possible to input data from a certain sheet, based the cell value of the main sheet. So what I have, is a main sheet that I use as the "Data Board". In the Data Board, is a ...
1
vote
1answer
22 views

Open multiple urls in different new browser tabs

My question is about the previous answer here: How to open multiple urls in different new browser tabs from Google Sheets with a single click? I just tried copying and pasting the code and running ...
0
votes
0answers
11 views

Copying a Google sheet into new sheets while maintaining the original Importrange data

I have 3 different master sheets - (1) Hours (2) Hours * Wages (3) Master labor $ Sheet Each sheet is connected. For example the hours are entered on sheet (1). Sheet (2) has the wages and ...
0
votes
0answers
16 views

Same value to return multiple match in Google Sheets

I don't know if the title reflect the situation but here it is. I have this kind of information (image below) where: Column A : 1 serial for 100 cells Column B : 10 serials for 100 cells Column C : ...
0
votes
1answer
33 views

Conditional formatting on whether something is a formula in Google Sheets?

Is there a way, in Google Sheets, to have conditional formatting of some sort, to change the background color of a cell based on whether the cell contains a formula or a plain value?
0
votes
2answers
984 views

ImportXML not loading due to number of requests

I am working on this sheet and it has a lot of ImportXML requests between columns N-Q. And because of the high number of requests, it seems like the ImportXML function is not working properly (it ...
1
vote
0answers
23 views

Get page number and page total (eg. Page 1 of 4) into the footer of a google sheet when exporting as PDF

In Google Drive Sheets you can use the custom header and footer tool to add custom headers and footers when you export a spreadsheet as a PDF. I would really like to get into the footer a statement ...
0
votes
1answer
281 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
2answers
35 views

App Script to Random Range with 10 % of total data

I have a table with 20 rows of data. I want to randomize the data with 10% of the total data. The script I made is like this: function shuffleSheet() { var ss = SpreadsheetApp.getActiveSpreadsheet()...

1
2 3 4 5
120