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.
8,674
questions
1
vote
1
answer
27
views
VLOOKUP of Text is not working
Here Is how my google spreadsheet looks like
I'm trying to find the the Artifact ID (Column K) using the artifact name that is in G
So on A2 I'm trying to use this formula
=VLOOKUP(G2;$K$2:$L$39;1;...
-1
votes
1
answer
33
views
How to retrieve accidentally deleted data from within a Google Sheets file on a private Facebook Group - please help
Someone accidentally deleted the data within a shared Google Sheets document on our Facebook group. Is there any way we can get it back i.e. revert to a previous version of the document? Is this ...
1
vote
2
answers
96
views
How can I link two addresses using a Google Sheets formula to create a Google Maps URL?
It seems that it is easy enough to get the URL to one address using the following formula:
=HYPERLINK(CONCATENATE("https://www.google.com/maps/search/?api=1&query=",L13),"MAP")
...
0
votes
1
answer
76
views
Google sheets - sum values in a key-value column pair for each key and display the results
Given:
A | B |
______|____|
key1 | 3 |
______|____|
key2 | 5 |
______|____|
key3 | 7 |
______|____|
key2 | 9 |
______|____|
Get the output:
D | E |
______|____|
key1 | 3 |
______|...
- 3
0
votes
1
answer
42
views
How to sum 5 largest numbers of 6 numbers across multiple sheets
I have a spreadsheet where we are keeping scores for golf across 6 weeks but we are only taking the best 5 scores from the 6 weeks. Each week (1-6) has its own sheet (tab)
I need to get the sum of 5 ...
- 1
-1
votes
2
answers
33
views
Subtotal of duplicate items in cell mentioning the duplicate items the last time
Currently, I am using the sum function to total matching item names by manually selecting matching cells. I would appreciate help automating the calculation of subtotals by item name.
Link to my ...
1
vote
0
answers
29
views
Edit and examine existing filter views in Google Sheets [closed]
I inherited a monster sheet that is pretty much constantly used by multiple persons. It contains several filter views.
Is there any way to find out what exactly each filter view does without manually ...
- 111
1
vote
1
answer
51
views
Trouble with a formula in Sheets to forecast shipment arrival
I have big trouble making my formula works and I am pretty sure that this is simple.
So simple that I am litteraly struggling on it for weeks now.. So I give up :'(
Let me explain:
I am using sheets ...
- 13
0
votes
2
answers
44
views
Return last value that isn't an error in each row
Trying to get some help with an issue I'm having.
In Google Sheets I have a data in columns A to D.
Column A contains my original list of names.
Columns B to D contain either a new name or an #N/A ...
- 1
-1
votes
2
answers
74
views
In Google Sheets, how do I add an extra leading zero to a list of numbers that are all written as #345 for example?
Formatting issue- my spreadsheet had numbers from #001 up to #850, but a new addition has taken the highest value past #1000, so now all of my old values need to change to four digit numbers. Is there ...
- 1
0
votes
1
answer
44
views
How to divide the values of a table in google slides by a number using App Script? [closed]
I have added a table from a google sheet to google slides using app scripts. However, I want to divide the values of the table by 1000000. Is there a way to divide the table values of the table in ...
1
vote
1
answer
67
views
How to QUERY - group SUMed results? [duplicate]
I'm new to query and can't figure out how to get grouped results of sum in two columns.
There's data set in the picture of names and points which are in two columns and query formula to sum and group ...
- 11
1
vote
1
answer
2k
views
Google Sheets: Create a formula to generate unique sequential IDs for items in a column that don't already have IDs
I am cataloging items in a spreadsheet using Google Sheets. Every item needs a unique ID, which is a sequential code formatted like ABxxxxxx, where AB000000 < ABxxxxxx < AB999999, starting from ...
- 13
1
vote
1
answer
277
views
ARRAYFORMULA and MAXIFS: does it work like SUMIFS?
I have an existing formula that is sort of a cumulative MAX of another column: it has a range covering only the rows above it, inclusive. So B2 is =MAX(A$1:A2), and I can run this down or auto-fill ...
- 13
0
votes
1
answer
27
views
Create a Chart for Membership Data
Was wondering how to best set up data to create charts for memberships.
I want to create a chart that compares # of New Members vs. # of Cancelled members. In the data, I have one columns that ...
- 1
0
votes
1
answer
258
views
Buttons don't work on iPad with Google Sheets - the onEdit(e) workaround isn't working. Suggestions?
I have a button that works with a function Yeah_Go(), but only when using on a browser on a laptop. When I try to press the button on an iPad Google Sheets mobile app, it doesn't work. Instead, the ...
- 103
-2
votes
2
answers
22
views
i want that webp urls are arranged in the same order as jpg urls
webp urls have the same names as jpg urls. i want that webp urls are arranged in the same order as jpg urls. they're in Y2:Y17 in google sheets. jpg urls are in Y2:Y9 and webp urls are in Y10:Y17. ...
1
vote
0
answers
22
views
Is it possible "conditionally format" an entire row? Visually group similar rows? [duplicate]
With Google Sheets is it possible to color rows by a given column.... for example to alternate colors based on key in this spreadsheet:
Key
Blah
Blah
Blah
1233
Blah
Blah
Blah
1233
Blah
Blah
Blah
...
- 696
1
vote
1
answer
27
views
Two Different Answers for the Same Simple Formula in Google Sheets
I have a spreadsheet with a very simple formula: =C4/D4
Sometimes, this very simple formula gives the wrong value.
Cell C4 contains the value 390062 formatted as a number with thousands separator
...
- 13
1
vote
2
answers
337
views
Google Sheets Conditional format when cell contains any word from another cell (partial text match)
I am currently using =regexmatch(A1,"(?i)N02|N22|J25") in column A to highlight the cells that contain these terms.
But I don't want to hardcode it as the search terms keep changing.
The ...
- 343
2
votes
1
answer
161
views
Google Sheets - Refer Filter range to a cell?
I have this formula (simplified because it is long):
=FILTER(RANGE1,(C3=TRUE), RANGE1<>"")
In place of range can I refer to the cell , AC2, that would contain the range name?
That is ...
- 343
0
votes
1
answer
27
views
Conditionally format the same cell in Google Sheets?
I will like the format the same cell in google sheet, to be more specific, any positive increase to the previous value (same cell) will turn green, and any negative decrease to the previous value (...
- 5
0
votes
1
answer
115
views
How to allow limited access to Dropdowns in Google Sheets
I work in an office. We use Google Sheets. I designed a sheet with three dropdowns to allow people to change info filtered by those three dropdowns.
I am the editor, and so are two other people.
I ...
- 1
1
vote
1
answer
66
views
Time series plot with category names as text
Let us say I have data like this for a spend summary sheet:
Date
Amount
Category
03/31/2023
20.0
Food
03/31/2023
23.12
Coffee
03/31/2023
21.01
Movie
04/01/2023
10.0
Coffee
04/01/2023
12.0
Coffee
...
- 113
0
votes
0
answers
10
views
How to open a specific tab based on todays month [duplicate]
I have a google document that i made a tab for each month like this, Aug, Sept, Oct, Nov, Dec, Jan, Feb, Mar, April, and May. I would like to be able to open the the document and automatically open ...
1
vote
1
answer
175
views
How to do an arrayformula that transposes a list while multipling the entries? [closed]
I have a list of rows, and I like to transpose them into a list of columns.
But the trick is how do I repeat each of the new columns 4 times (1 row will become 4 columns)?
Alternatively, how do I add ...
- 1,204
0
votes
1
answer
51
views
Google Sheets conditional formatting gradient not applied for values >= 1 and < 2
I have a Google sheet where several columns use a gradient (using traffic light colours - from red to yellow to green) to indicate values from min to max.
This works fine for values less than one, and ...
1
vote
1
answer
27
views
Single Google Sheet COUNTIFS function that counts across multiple columns with multiple conditions
I want a sheet that counts across multiple columns
Here's my Google Sheet
H M P U
Submitted 3/3/23 John Validation
In progress John In progress
I want to use a ...
- 449
0
votes
1
answer
207
views
Wrong grand total when using Calculated Field
I have a simple pivot table, but I'm using Calculated Field values.
The raw data used for the pivot table is like a transaction log, as shown below:
DATE
STOCK
QTY
PRICE
TOTAL
03/2023
AAPL
1
100
100
...
- 53
0
votes
1
answer
37
views
Trying to round a number to the nearest millions
I have several hundred rows of revenue data that start from row 1 and goes all the way down the sheet in column A. Something that look like this...
A
B
$135,345,000
$200,000,130
$383,430,000
$4,...
- 5
0
votes
2
answers
99
views
How to Count Combinations In a Set (Including dupes)
Trying to figure out a formula where I have 5 columns of text data. I want to count how many times combinations of data in 2 contiguous columns appear in each row.
Example data:
A
B
C
D
E
1
A
A
A
B
...
0
votes
1
answer
11
views
in google sheets, automatically sum up sales between dates
In Google Sheets, how can I automatically sum up the sales from a particular area from the start date to end date (sample: March 3 to 10 or March 2 to 15)? Is it possible to just type the area and the ...
0
votes
1
answer
24
views
SUMIF sometimes not summing when criteria is met
SUMIF is not summing some of the cells despite the logic being met. For example, cell B9 on sheet 3 should result in 25, but it is coming back with 0.
I've tried
sumif(Sheet2!$B26:$O26, "*MW*&...
- 1
1
vote
1
answer
200
views
Change colors on graph
I have a graph on google sheets, and want to change the background color for certain values on x axis. For example, from a value A to a value B (on x axis) I want a background color 1, then from ...
- 11
0
votes
1
answer
29
views
Add hourly rate to line items based on line item date
Hello, I'm trying to set up a system using google forms and google sheets to keep track and analyse my business's employee's hrs and wage.
My idea is:
Use a simple Google Form to for employees to ...
- 1
0
votes
1
answer
23
views
on google sheets, use "this line" instead of line number [duplicate]
I have the following formula on a google sheet:
IFERROR(Vlookup(A3,INDIRECT(A$1),1,false),0)
My desire is to have this formula use A3 on line 3, A4 on line 4 and so on
However, sometimes I get it ...
- 277
0
votes
1
answer
63
views
Return Value to Different Line with Possible Duplicate Information
In Google Sheets, I need to extract values of D in specific rows and insert them into column I where appropriate.
See link below. Here is an explanation of what I have so far in the spreadsheet:
Raw ...
- 15
0
votes
0
answers
11
views
How to calculate a reverse exponential graph? LN function? Log Function? [duplicate]
I have a lot of revenue data across thousands of companies down row A below (for this example I'm showing just 3). We are trying to understand a company's "Total Addressable Market" (TAM) ...
- 5
0
votes
1
answer
21
views
SUMIF for unique entries
I need to do a SUMIFS statement in Google Sheets but am having a hard time not double counting duplicates in the data. For example:
Volume
Flavor
Batch #
6000
CLM
BT1000
6500
CLM
BT1000
7000
WBG
...
- 13
0
votes
1
answer
59
views
simplification of a fomula using offset
I want to use a less complicated formula to achieve
the answer to cell D28 in this sample spreadsheet
I was wondering if there was a way to group the offset cells together in a set so I didn't have to ...
- 99
0
votes
2
answers
2k
views
Have multiple selections in drop down Google Sheets [closed]
I'm trying to make it so that I can select two types of membership for one individual (e.g. volunteer and family).
- 11
0
votes
1
answer
51
views
Google Sheets: Splitting siblings by different @class attributes into columns using IMPORTXML formula
Good day, everyone! ^^
I am still a beginner in Google Sheets formulas and still don't understand many things, but I am trying to scrape sample sentences from an online Japanese dictionary using ...
- 65
-1
votes
1
answer
34
views
"HOW TO KEEP FORMATTING ON A SPECIFIC CELL/ROW (USING SORT FUNCTION) AFTER NEW DATA IS ENTERED"
I'd really appreciate some help with the document.
I'm using the auto sort (SORT function) on the second sheet and would like to keep the conditional formatting to a certain cell/row after a box is ...
- 1
0
votes
1
answer
19
views
Help getting multiple rows of data from a selected drop down list
I have the following rows of data in my sheet. Here's 7 as an example, but I essentially have over 1000 rows of data.
Column A
Column B
1
Tesla
Jess
2
Toyota
Kelly
3
GoPro
Jess
4
Apple
Linda
5
...
- 5
0
votes
0
answers
61
views
Reference other sheets in Google Sheets using file name? [duplicate]
I have a set of Google Sheets workbooks in a Google Drive folder that reference each other. One of the source workbooks contains data that is externally updated daily which then replaces the same ...
user1779906
1
vote
1
answer
1k
views
Cannot find active sheet: TypeError: read properties of undefined (reading 'source')
I'm trying to add the current date to a cell in column K when any cell in that respective row is changed.
I found this script and altered it for my situation so I can deploy it as a trigger:
function ...
- 449
1
vote
1
answer
75
views
Why are the share button and toolbar blue in Google Sheets? [closed]
Here is the capture.
But the share button and toolbar look normal if I use another account.
- 11
0
votes
1
answer
22
views
Insert formula, process and replace by raw value
What is the best approach to achieve the following:
If a cell in column A has content, then place a formula into the cell in column B in the same row, referencing the cell in column A.
Once the ...
- 5,438
0
votes
1
answer
25
views
Google sheets. Compare the value in column on the 1st sheet with 2 others and then pull cells to y the right of matching value in the other sheets
I have one workbook, 3 sheets. Ultimately I'd like information from sheet 2 and 3 joined with corresponding information on the 1st sheet.
The 1st column has an ID number on each page. How can I make ...
Ariceliz
0
votes
1
answer
262
views
Google Sheets Pivot Table - Show totals for only one column in grand total
I have a pivot table that has 1 Row (ATIVO), 0 Column and 4 Values columns (Quantidade, Preço Un. Atual, Preço Médio and Total).
But I only need the grand total (shown as Total geral) from the last ...
- 53