Skip to main content

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.

Filter by
Sorted by
Tagged with
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 ...
Beth Maxwell's user avatar
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 ...
Anand's user avatar
  • 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: ...
John's user avatar
  • 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....
Keith Makowski's user avatar
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....
Keith Makowski's user avatar
-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 ...
TheBeeej's user avatar
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 ...
Jimmy's user avatar
  • 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....
user3093834's user avatar
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( ...
andio's user avatar
  • 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 ...
Ednoc's user avatar
  • 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 ...
Jim's user avatar
  • 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?
peterK88's user avatar
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 ...
user343166's user avatar
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 ...
Josh Carter's user avatar
-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 ...
MOON's user avatar
  • 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})") ...
MOON's user avatar
  • 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?
Hassan Almaateeq's user avatar
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. ...
Hassan Almaateeq's user avatar
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 ...
Hassan Almaateeq's user avatar
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 ...
Bryson Williams's user avatar
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'...
Marie's user avatar
  • 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 ...
jcollum's user avatar
  • 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' ...
Kris Middleton's user avatar
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 + (...
AxelShip's user avatar
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 ...
Toby Fournet's user avatar
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)...
Beth Maxwell's user avatar
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 ...
Ramya's user avatar
  • 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($...
Oliver's user avatar
  • 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 ...
Laura's user avatar
  • 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 ...
Beth Maxwell's user avatar
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:
Franck Dernoncourt's user avatar
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&"...
Beth Maxwell's user avatar
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. ...
user avatar
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 ...
Hassan Almaateeq's user avatar
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 ...
ŠüR0's user avatar
  • 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=...
kferd's user avatar
  • 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 ...
Charlie Collins's user avatar
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: ...
Harumi Orozco's user avatar
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 ...
Nyah's user avatar
  • 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 ...
orschiro's user avatar
  • 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 ...
Nyah's user avatar
  • 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 ...
Ramya's user avatar
  • 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, ...
Michael Peterson's user avatar
-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 ...
Michael's user avatar
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-...
Dan Rye's user avatar
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 ...
Ramya's user avatar
  • 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 ...
Ramya's user avatar
  • 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 ...
NateM's user avatar
  • 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 ...
Robert Etropolsky's user avatar
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 ...
Qiao Hui Quek's user avatar

1
2 3 4 5
181