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
0
votes
1answer
9 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
0answers
4 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 //...
-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
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 ...
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 | | ...
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
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 ...
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 =...
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 ...
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
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: How can I compare two ...
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 ...
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
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 ...
-2
votes
0answers
11 views

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 ...
-1
votes
0answers
8 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 ...
0
votes
0answers
7 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 ...
0
votes
1answer
11 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
0answers
17 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
0answers
12 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
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'!...
0
votes
1answer
13 views

Google Sheet day() formula returns wrong day name

I want to get Day name from a specific date on Google Spreadsheet (also tried on Microsoft Excel Online). I have written specific date on a cell B1: 2020-03-31 then custom number format it as: yyyy-...
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 ...
0
votes
1answer
15 views

Find-Replace with discontinous ranges

I am editing a Google Sheet with multiple peers. I need to do a Find-Replace on just my rows, say, rows 1:38 and 197:222. (Not permitted to sort the data.) I see many examples on the web of doing this ...
0
votes
1answer
18 views

Can't get If Else statement to run based on value of edited drop-down selection in Google Sheets Script

I have a dropdown field on a sheet (consisting of Wheat, Wood, Stone, etc.). When that dropdown field is edited, I want it to run one of a few similar functions, based on the dropdown selection (when ...
0
votes
0answers
14 views

How to rename a series in Google Sheets chart?

By default, a Google Sheets chart will use the first row as the the name of the Series. But if you are selecting a range in the middle of the table (for example, rows 38-100), then you can't use the ...
1
vote
1answer
17 views

get value left of max value

I have a sheet that looks like: Date Value Highest Lowest ---------------------------------------------------------------- 1-1-2020 23 26 20 2-1-2020 20 3-...
0
votes
1answer
12 views

Is it possible to highlight the minimum value in a table on Gsheets?

I am currently compiling a table of pace per minute on this Gsheet. I am looking to conditionally format the cells to highlight the minimum and maximum but cannot seem to find a formula for this ...
0
votes
1answer
7 views

Decompose a column into multiple columns with TRUE/FALSE values instead

Here is a very small version of what I want to achieve but not able to figure out what exactly should I Google search for - Column A | Column B Alice | Present Bob | Absent Should ...
1
vote
0answers
18 views

Why might typing in the Google Sheets formula field occasionally write over text as if Insert keyboard feature was on when it is not?

Within the last couple of weeks when working on various different sheets on different desktop computers I have noticed that as I type in the formula field, text is overwritten on a random ...
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()...
0
votes
1answer
15 views

Recurring event every x hours in Google Calendar?

I need to create an hour-long event in Google Calendar that repeats every 124 hours, but there is only an option to repeat days. I did some research, and it might be possible with some spreadsheet ...
0
votes
0answers
14 views

How do I get a list of all values in the same column across multiple spreadsheets within the same Google Sheet?

I have a Google sheet that has multiple spreadsheets, let's say Sheet1, Sheet2 and Sheet3. In all of those spreadsheets, column H is a particular field of which I want all the values with blanks ...
0
votes
0answers
15 views

Sum and calculate percentages of cells with specific values

I'm trying to develop a marking sheet that will return percentages of checked cells, but discard any unchecked cells. So far I've got the checked cells done - I assigned checked cells a value of 10 ...
0
votes
0answers
20 views

Google Forms: How do I add “if yes, please describe” to a yes answer if chosen? [duplicate]

When using Google Forms, if the person answers yes, I want him/her to elaborate and give a short answer. How can I do this?
0
votes
0answers
10 views

Copy and paste queries/formulas from one column into a new column

I have a bunch of formulas in my column's cells. I want to make a duplicate column with the same queries/formulas, and if I ever make a change in one of the cells in the column, it applies to all the ...
0
votes
0answers
21 views

Using Google Forms and Security Settings

We are thinking to use Google Forms and we need to find out if it can be used for collecting and processing personal information such as Name, Address, Phone, etc. Could someone could make a ...
0
votes
0answers
21 views

How to link a script that I made on my PC browser to work on my mobile app

I have made a script, with thanks to Tedinoz on here. I have now also created a button using drawing and adding the script to it That button shows on my mobile app but when I press it I only get a ...
-1
votes
0answers
14 views

How can I generate a login report using CSV data in google sheets?

I'm using an LMS platform that can generate login reports on a daily and weekly basis. I'm sharing these reports with our attendance people, but the data is presented in a really messy way. I would ...
-1
votes
0answers
30 views

Count if in Google script

How to Calculate the number of occurrences of products in order from top to bottom (C column)? How is it possible to write a simple script to know the B column. https://docs.google.com/spreadsheets/d/...
0
votes
1answer
16 views

GOOGLE Forms and Mapping Location

Can you use Google Form to collect information about the activities of users and drawing a map showing a radius where these activities occurred? Like show me a map that shows all the stores under ...
0
votes
0answers
13 views

Convert a field in a Google Spreadsheet document from text to number using a formula ignoring text but keeping numbers

I have a Google Spreadsheet document with a column which contains string data which includes both numbers and text. For example: $39,90 + Tax $69,90 + Tax $20 / month I want to sum all those values, ...
0
votes
0answers
5 views

Multiple y-values for one x value

I'm trying to graph the following values: 0degr 0.24-0.27 20degr 0.22-0.28 40degr 0.22 60degr 0.2 How do I graph a range such as "0.24-0.27" in google sheets? Thank you!
0
votes
0answers
14 views

How can I highlight a cell if another column contains the same text?

I have a spreadsheet containing a list of names in column A. I also have a list of names in column C. I'd like to create a conditional formatting formula to highlight any cells in column A that ...
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
0answers
31 views

Google Sheets - Data importing

I was wondering if someone could help with this scenario: On Google Sheets, I am looking to take data from Sheet 2 and place it in Sheet 1 (Master list), so the data in sheet 2 is temporary and has 5 ...
0
votes
0answers
13 views

Spreadsheet formula for “un-pivoting” some 2-dimensional tables to 1-dimensional columns?

Apologies in advance for not using any correct terminology as I don't actually know the best words to describe this problem. I have two equally-sized 2-dimensional regions on a spreadsheet that have ...
0
votes
0answers
41 views

Copy protections

I have a sheet with formulas in certain cell ranges. This spreadsheet is shared with multiple editors with edit permissions, but to protect formulas I have locked these ranges to prevent editing. I ...
0
votes
1answer
32 views

How to add a note or comment into a formula in Google Sheets?

Microsoft Excel Microsoft Excel has this feature where one can add a note into a formula with the N() function. It looks like this: Source: Add Comments to a Formula in Excel for Your Future ...
0
votes
0answers
14 views

Find a row based on cell value and then count within that row

I'm currently working on a Google Sheet for work, in which I'm trying to work out whether each student has done a particular paper or not. I want to have two sheets. One sheet will have the students ...

1
2 3 4 5
120