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 data as markdown table to your question; this will greatly increase the chance that you get a satisfactory answer.
9,007
questions
1
vote
1
answer
30
views
How can I get VLOOKUP to return a response that is in one of two adjacent columns?
I have the following working formula in my header row and that returns an array of "Project Team Owner" values from my Form Responses sheet based on a matching Unique ID:
={"Project ...
- 29
0
votes
1
answer
18
views
Is it possible to add Dynamic subtotal in a google sheet without app script?
Please look at the google sheet below. In the first column it consists serial numbers, 2nd column contains company name. 3rd column contains name of the assignee to which the company is assigned and ...
- 21
0
votes
2
answers
30
views
Which division is correct? Google Sheets or Calculator?
C16
Google Sheets -> 73%
Calculator -> 73%
F16
Google Sheets -> 72%
Calculator -> 74%
Row 16 is the sum of rows 1-15.
C16 and F16 are the sum of rows 1-15, then divided by 15.
F16 Formula: ...
- 103
-1
votes
0
answers
10
views
Delete or Remove Rows based on search data, these codes worked individually but will not work when combined to one script. Why? [closed]
function removeRows() {
var sheetName = "DBDR_AllDevicesTAB"; // Replace with your sheet name
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var range = sheet....
0
votes
1
answer
17
views
Finding text with strikethrough and delete its entire row
I am trying to find any cell on active sheet and delete its entire row. Does anyone have suggestions?
function fontLine() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet....
-2
votes
0
answers
18
views
Adding conditional formatting to charts in Google Sheets
Newbie here. I know it's been discussed, but wondering if there's a script out there that can modify/add conditional formatting to data in a Google Sheets chart? Whatever I have for conditional ...
- 1
1
vote
1
answer
30
views
Conditionally format specific values within comma-separated values
Is there a way to apply conditional formatting to specific values within a cell of comma-separated values?
Background
In a Google form, I have a Checkboxes-type question with 50 options that ...
- 11
0
votes
1
answer
42
views
Looking for help on formula while pulling data from a different GSheet using Index & Match?
Facing a #NA error while pulling data from a different google sheet, using Index & Match. Need Test1 (GSheet) data pulled in to Test2 (GSheet) on yellow highlighted cells.
Test1 - https://docs....
- 35
1
vote
1
answer
20
views
Insert sequence number and embed it to my data
I have this data:
A
B
1
X
BIRD
2
X
LION
3
Y
FISH
4
Z
CHICKEN
5
X
DUCK
6
Y
SNAKE
7
X
TIGER
8
X
CROCODILE
9
Z
ELEPHANT
10
Z
DRAGON
11
Z
WORM
I use this formula:
=index(
trim( split( ...
- 419
-1
votes
1
answer
22
views
Generate a customized ID showing 10 numbers
In Google Sheet, I would like to be able to generate an ID/serial number like this:
0000000000A1
Each incrementation should add 1, like this:
0000000001A1, 0000000002A1, 0000000003A1, etc...
By using ...
- 101
0
votes
1
answer
22
views
Calculating total score of a column
I have a google sheets with the following format:
A list of rows that list criteria
A list of cols per row that either have a numerical value from 1-10 or a yes/no answer (I guess could be represented ...
- 123
0
votes
0
answers
16
views
When I freeze the first two columns of a sheet only the first column is visible in the mobile application [migrated]
Google Sheets for Android only displays the first frozen column. The second and subsequent frozen columns are not visible.
How can I unhide them?
- 15
0
votes
1
answer
17
views
Data from second importrange function is in the wrong column
When I import 2 ranges that are each in Columns A to L in their respective sheets, the second range is being returned in Columns M to X.
How can I fix it so the data from both ranges is returned in ...
3
votes
2
answers
36
views
Formula for calculating cumulative cost with a fixed rate
I'm trying to figure out a formula I can use in Google Sheets for calculating the cumulative cost of a vendor where my monthly bill increases by the same amount each month and costs grow over time (ie ...
- 33
-3
votes
1
answer
21
views
How to aggragate data based on a keyword
I want to use Google Sheet as a time tracker. That is, in a cell I will write:
keyword_A 13:00-14:12
keyword_A is one keyword, among many, separated by space and then the starting time of the ...
- 95
-1
votes
1
answer
18
views
How to use Google Sheet as a time tracker
I want to use Google Sheet as a time tracker. For example, in a cell, D3, I enter:
Gym 17:00-18:00
I can extract the hours spent in Gym by:
=REGEXEXTRACT(D3,"Gym (\d{2}:\d{2}-\d{2}:\d{2})")
...
- 95
-1
votes
1
answer
10
views
Sharing a single tab in Google sheet as a viewr? [duplicate]
Is it possible to share a single tab in Google sheet without being able to view other tabs?
- 473
0
votes
0
answers
20
views
Overcome the latency/lag of (=importrange) function [duplicate]
We know that importrange function in Google sheet has some latency in refreshing the data continuously and can reaches upto one hour in order to update any changes made on the reference page/sheet. ...
- 473
0
votes
1
answer
19
views
How to add a Google sheet time driven trigger that runs offline?
I'm using two Google sheets lets say sheet A (main data) and B.
Sheet B retrieve data from sheet A using importrange and query functions and data should refresh every day at 7:30 am.
There is a ...
- 473
0
votes
1
answer
17
views
Dynamic data range column in formula based on header value in google sheets
Problem: I need to summarize hundreds of sheets that all contain the same column header but are not always on the same column number. Is there a smart way to get the column for a range based on what ...
0
votes
1
answer
7
views
Match criteria THEN CountIFs
I am trying to create a formula in Google Sheets for a list of account names.
In Sheet1 I have a product list where each row contains a product, its status,m and associated account.
In another sheet I'...
- 1
0
votes
2
answers
35
views
How do you fill a column with an alphabet series?
Given A to E in a column, how do I fill the rest of the letters up to R (or Y or Z etc) without typing them one by one? The blue dot handle doesn't seem to be able to do this, it just repeats the ...
- 437
0
votes
1
answer
28
views
I'm looking fSSearch bar look for partial matches including letters and special characters
really finding it hard to obtain a script that does this. I've created a spreadsheet that has a data entry form 'User Form' referring to another sheet that is where the data is submitted to 'Database' ...
1
vote
1
answer
34
views
How can I round up numbers in increments of 0.5?
In Google Sheets I'm trying to calculate variable shipping costs.
Shipping cost is calculated by adding a fixed base price to the product of the item's weight in kg and the rate per kg:
cost = base + (...
- 13
1
vote
1
answer
24
views
Break out data from one row to multiple rows on Google Sheets
I am currently building a sheet to collect data from an online form and need to have individual records for each division a person is in.
The data comes in like this:
ID Number
First Name
Last Name
...
- 11
0
votes
1
answer
26
views
Can you do an arrayformula when using index match?
I have the following index match formula in this sheet.
Project Tracking Tab, column B
=IFERROR(INDEX('Project List'!A4:B,MATCH(A2,'Project List'!A4:A,0),MATCH('Project List'!B4,'Project List'!A4:B4,0)...
- 29
1
vote
2
answers
17
views
Filter Rows while also doing SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets
I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports.
Below is the sample data i have.
As per the guidance from the ...
- 29
0
votes
1
answer
17
views
Googlesheet formula
I'm trying to make this formula as one statement, what formula should I use, thanks.
=IF(AND($E2="Simple",$G2<=3),$M$2,$M$5)
=IF(AND($E2="Complex",$G2<=7),$M$3,$M$5)
=IF(AND($...
- 1
0
votes
1
answer
15
views
Is there a way to have Google forms only show one persons responses in sheets? Need to be confidential
I work at a University running student programming. Students in the program are required to attend events on a regular basis, and submit a response through a form verifying their attendance.
This year ...
- 1
0
votes
1
answer
22
views
Help converting counta to an array formula
I need help adding turning my formula into an Array Formula
I am using the COUNTA to count all cells in row range that are not empty ignoring blank cells with formulas. It works, but will not copy ...
- 29
0
votes
1
answer
29
views
Sort range in Google Sheets
I use Google Sheets. I have a column in random order:
How can I sort it like this:
and not:
- 18.9k
0
votes
1
answer
19
views
My unique ID is not copying down when using arrayformula
Hi I am using the following formula to create a unique row id using values in other cells in my Google Sheet. When I add arrayformula it does not copy down. What am I doing wrong?
Works
=B2&"...
- 29
0
votes
2
answers
18
views
I have a named range on google sheet and I want to access the informations in it from a different sheet with if condition
Let's say I have 2 sheets. One with character stats and one with the tables for the character data.
One sheet is called "Character" and it has 4 columns of data: Level, Class, HP and Mana.
...
Max Lagarde
0
votes
1
answer
28
views
Is there a way to (query) values from merged cells in Google sheets?
Sheet
A1 is merged with B1. They contain a value "John"
Is there a way when I type =B1, the value of A1 is the result?
I mean whenever two cells in a row are merged, the value of the ...
- 473
0
votes
1
answer
34
views
How can I automatically select and download rows as CSV based on checkbox states and then modify checkbox states after download?
I have a Google Sheet with 20 columns.
Columns C and D contain checkboxes that indicate the states of two separate row status':
checkboxes in C are for READY status.
checkboxes in D are for EXPORTED ...
- 31
0
votes
0
answers
18
views
'importhtml' function on google sheets is only returning headers but not data from the body of the table? [duplicate]
I am trying to extract the data from a table on the following website:
https://www.fangraphs.com/leaders/major-league?pos=all&stats=bat&lg=all&qual=20&type=1&season=2024&month=...
- 1
0
votes
0
answers
6
views
Formula that can add X number of rows (based on the value in another cell)
Hoping you can help - I have a formula that will build table rows (here).
But I have data that is slightly different and hoping there's a way to adjust the formula to make it work.
Option 1.
I ...
0
votes
1
answer
28
views
How to remove the first letter from each alphanumeric string in a column?
I have a list of 107 alphanumeric serial numbers that have an extra "S" as the first character that I want to remove.
For example, if a serial number is:
SFNS28O413AD
It would become:
...
0
votes
1
answer
20
views
Google Sheets Conditional Formatting w/ Several Criteria
I posted a similar question here yesterday, but didn't realize that I should have included more info/conditions.
Basically I am starting with a spreadsheet of timesheet data. Each row is a single ...
- 13
0
votes
1
answer
46
views
How can I filter the results of IMPORTFEED based on a range containing a list of keywords?
I have the following working Google Sheets IMPORTFEED formula in a sheet named 'RSS':
=IMPORTFEED("https://www.wertpapier-forum.de/discover/1431.xml",,,100)
I want to filter those results ...
- 5,590
1
vote
1
answer
24
views
Google Sheets Conditional Formatting w/ Multiple Criteria
I'm trying to format a spreadsheet containing timesheet data to flag certain conditions.
Each row of data represents a single pair of clock-in and clock-out times for an employee.
There can be several ...
- 13
0
votes
2
answers
42
views
SUM columns based in column header values as part of Select statement in Query function in Google spreadsheets
I am trying to set up a pivot table using Query function in Google Spreadsheets, so that this can be used for various different reports.
Below is the sample data i have.
I already have this below ...
- 29
0
votes
1
answer
25
views
I am tying to build a multi-dynamic GOOGLE sheet query and have included a sample sheet
I have been working on this for HOURS and can't seem to figure out the piece I am missing.
Basically I have a sheet that I have built that has 8 tabs of representatives of 8 different companies and, ...
-1
votes
1
answer
12
views
How to write a formula returning from specific
I am working on a Google Sheet. Here I have a somewhat non standard table. To cut it short, I was able to designate "1" in column A for each row that fulfills my requirement.
Imagine that ...
- 1
0
votes
2
answers
16
views
Reformat a Google Sheet based on Data in Column A
I'm trying to work out if there's a way to reformat a sheet, so that the name data relating to a specific order number in Column A, is put into a single row (so that I can perform a subsequent data-...
- 1
1
vote
1
answer
25
views
Apply conditional formatting when the value in a row increases or decreases
I am trying to apply conditional formatting to a range of data, but not able to find a solution for it.
I am trying to do two types of formatting.
When a value in a row is less than the value in the ...
- 29
0
votes
1
answer
23
views
How to pull all the data on the "right" of a column with certain value in the header
I am trying to pull out certain columns of information from a autogenerated report for further analysis. I will need to only pull certain columns based on the header values.
Here is the link to the ...
- 29
0
votes
1
answer
17
views
Checking to see if a number falls within a range
I have a Google sheet that has two tabs and I am trying to build something that takes column B from the Sheet2 tab and cross-references Sheet 1 and highlights or lists on Sheet2 the ones that fall in ...
- 13
0
votes
0
answers
16
views
Insert clickable link within a line of text from another cell
So... i have a sheet keeping track of details for real estate properties. Of relevance here:
Street Address
Cross streets
City
And i have a MAP cell in which i've created a URL that links to a google ...
0
votes
2
answers
34
views
Return a list of names if a column in the same row is empty
I have the following values in Sheet A:
A B
1 Josh Awarded XP
2 Jane (blank)
3 Peter (blank)
4 Mary (blank)
5 Josh (blank)
6 Peter (blank)
I want to reflect the above ...
- 103