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.
5,992
questions
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 ...
