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 a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.
8,267
questions
0
votes
1
answer
6
views
importxml script not working outputs formula not the result
I've tried to get importxml into a function so that I can get a specific piece of info from a website, but the result I get is the formula not the result of the formula/function, here is my script, ...
- 1
1
vote
1
answer
20
views
Is there a way to make an extension from a google sheet cell?
I don't really know how to describe this, but let say on one cell from google sheets you are able to click on it and a list of additional cells pop up (not related to the other cells), and this new ...
- 111
3
votes
1
answer
26
views
Add a script trigger to Google Sheet that will work in Android mobile app for merged cells
I'm new here, and I have my first question regarding the following post here:
Add a script trigger to Google Sheet that will work in Android mobile app
function onEdit(e) {
if (e.range.getA1Notation(...
- 33
0
votes
1
answer
16
views
Point-to-point geographic chart from latitude and longitude coordinates in google sheets chart
Is there any way of creating a point to point geographic chart from date of origin point to destination point in google sheets?
Consider the following data:
To map something like this, though I don’t ...
- 101
0
votes
1
answer
15
views
Why is Sort range not working?
Minimal example: https://docs.google.com/spreadsheets/d/168bq72DGKNxNLXt-Jzbh95iQ2Gu93IzmPp38SB0od5E/edit?usp=sharing
inProduction velocity(%)
TRUE 0.027192
TRUE 0.026422
TRUE 0.246903
...
- 101
1
vote
1
answer
10
views
How can I use the filter option to only view names of agents that are in particular groups?
On the top rows are some groups that each agent is assigned. What I want is to use some kind of tool or formula that can display columns of those agents that have the desired groups. For example, if I ...
- 11
1
vote
1
answer
13
views
How to make uniqueness validator ignore header and not ignore text case, in Google Sheets?
I have a data validator which is like this:
=COUNTIF($D:$D,"="&D1) < 2
Notice that meaning is invalid. That is only because it appears in the header. Also, notice this:
It doesn't ...
- 189
1
vote
1
answer
11
views
Google Sheets, group by unique values in Column A, then show all values in Column B that are in the group
I have rows of raw data, and I would like the group by column A, and then for each group, list out the values of column B that are in that group. Duplicates in column B (Likes to go to) are fine, and ...
- 111
1
vote
2
answers
12
views
List words in a phrase that also appear in a range
I need help with Google Sheets to search part of a cell within a range. My formula works in Microsoft Excel but not in Google Sheets:
A
B
C
dog
this cat is very friendly
=LOOKUP(REPT("z",...
- 11
-1
votes
1
answer
20
views
Automating a macro with an IF condition
Is there a way to use an IF condition to copy and paste certain cells into another sheet tab in Google Sheets?
I was thinking about running a macro, but not sure if I can run a macro with an IF ...
- 1
1
vote
1
answer
18
views
Pie chart in Google Sheets has 100% one item
How can I create a pie chart in Google Sheets from 2 cells?
Total cash
Total funds
40
60
When I create a chart it comes out as 100% one item.
- 273
0
votes
1
answer
33
views
Google Sheets multiple IF cells to fill out other cell
Good day everyone
For my coworkers, I am creating a Google Sheet for their follow-up files. However, I'm having an issue.
There are multiple cells to look at to fill out details in another cell. I got ...
- 1
1
vote
1
answer
17
views
How does Google Spreadsheet preserves formatting in external Copy-Paste?
Note: This is on MacOS. I don't know if it is repeatable on Windows.
When I copy a cell to a text editor and back, formatting (and links) are preserved. Even links, while don't appear in the text ...
- 113
0
votes
0
answers
18
views
Need help with client purchase data - Google Sheets
I work in sales for a small brewery. I'm trying to put together a spreadsheet that will tell me how often each of my wholesale customers purchases each item that they stock and in what quantities.
...
- 1
1
vote
1
answer
20
views
How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty
I need your help with conditional formatting in Gsheet or Excel (I will choose one of them depending on the solution).
This format needs to be: Any empty cell in columns C to L must be highlighted if ...
- 11
1
vote
0
answers
27
views
How to combine multiple sheets and sort the results in Google Sheets?
I have a "main" sheet like this (which is single words, not compound words):
And I have a compound word sheet like this:
Notice the slightly different columns. How do I combine both lists, ...
- 189
1
vote
1
answer
9
views
Which formula can add the same word next to the query result lines // example
I made an example to support what I am trying to explain in the title.
File is https://docs.google.com/spreadsheets/d/1yD49_As8nM6gLq2A1J9eEEoIVZadxfVQBq-LPSDIPLA/edit#gid=0
The objective is : I query ...
- 11
1
vote
1
answer
18
views
How to fetch a value from a cell and use it as a prefix for a term in another cell?
I have 2 sheets in a spreadsheet, 1 which is the list of all the words / definitions, like this:
And another that is a bunch of derived words, like this:
In the derived word sheet, I would like for ...
- 189
0
votes
1
answer
15
views
List of VLOOKUP items [duplicate]
I have a ledger spreadsheet that contains name of people and sports that they practice, which is organized like this:
column A | column B | column C
John | Soccer | Q1/2021
John | Soccer | ...
- 515
1
vote
2
answers
15
views
generate sequence like values from a column that generate the same sequence-value for the same column-value
I know, the title is an absolute desaster, but I dont know how to describe what I want with just a few words :(
Assume that I have the following table:
column a
column b
87
1
11
2
87
1
10
3
3831
...
- 113
0
votes
1
answer
14
views
Is there a way to figure out how much a person owes across multiple ranges?
I am trying to figure out how much Albert Hanes owes in total, would I use some type of vlookup formula or would I use something else?
https://docs.google.com/spreadsheets/d/...
- 1
0
votes
0
answers
14
views
Why does line graph turn gray when chart is deselected?
I have a line graph in Google Sheets with 3 separate data series where each line is a different color. Two are working fine, but the yellow line always turns gray when I deselect the chart. When I ...
- 216
0
votes
1
answer
23
views
How can I find the first empty cell in a row?
I have a simple spreadsheet with an absence calendar (https://docs.google.com/spreadsheets/d/1PfuGPIyWawiBE2Czz9_PPKFk59Vn9eNUF2PFiXu2Wu8/edit?usp=sharing) that shows me if a person is absent today. I ...
- 1
0
votes
1
answer
15
views
How do I make a list with repeating entries?
Essentially, I want a formula which creates a list of jobs based on the value given to that job.
Here's an example.
I have a column for jobs which lists Barista and Manager. Then a column for the ...
- 3
0
votes
1
answer
22
views
How to add date stamp when criteria met in google spreadsheet?
This is the current script. When a cell in column 26 is edited, a datestamp is added to the cell beside it in column 27. Now I would like to add additional criteria. Example: When col 26 value is &...
1
vote
0
answers
36
views
+50
How to fix text alignment issues in Google Sheet?
I have a spreadsheet with a few text alignment problems, here are two images:
Notice:
TRUE and FALSE are centered (probably because they are keywords.
Some hebrew/arabic words are aligned on the ...
- 189
0
votes
0
answers
8
views
Link to a filter view from within sheet in Google Sheets?
I have a sheet like this, where G is the total count for the filter view.
This filter view is for "chinese" words, but I have about 10 others for 10 other languages. Clicking through the ...
- 189
1
vote
1
answer
15
views
How to add row count when using filter view in google sheets?
I have a sheet with a master list of dictionary words, and their language. I then create filter views for each language. Here is the Sanskrit filter view:
First off, notice the row numbers are the ...
- 189
1
vote
0
answers
12
views
Freeze the values of a slicer when I leave the sheet
I have multiple pivot tables and each of them is linked to one or two slicers. They adjust when I filter by values or conditions but when I want to go to another sheet of my document.
The problem is ...
- 11
2
votes
1
answer
27
views
How to make a drop down dependent on a dropdown?
I work with a building company. I'm attempting to keep track of the Job Site and the Work completed at that Job Site by the labourers. At the moment I have a drop down with a long list of Job Sites, ...
- 21
2
votes
1
answer
14
views
Query to extract unique values from 1 column and add matching numeric values
I have multiple sets of 2 columns (COL_LEFT and COL_RIGHT) and would like to extract all the unique values (except the blanks) from COL_LEFT and display matching values from COL_RIGHT alphabetized by ...
- 51
2
votes
1
answer
18
views
Using INDIRECT and a cell reference to compare values on sheets
Simple story - driving me cray cray.
I have a single Google Workbook with sheets within with separate names (Sheet1, Sheet2, etc).
I have a separate sheet called Compare that I want to make formulas ...
- 23
1
vote
1
answer
22
views
Generate Random numbers only once when spreadsheet is opened
I'm struggling with rand() and randbetween().
This is what I'm looking for:
I open my spreadsheet, it generates 20 differents seeds (some between 1 and 12, some between 1 and 6, some between 1 and 3 ...
- 13
1
vote
1
answer
10
views
Google Sheet Filter View Sorting gets turned off when I edit a cell?
I just converted a sheet to use filter views. It has a language column, and in this example I am filtering to show only Hebrew words.
I added "Sort A -> Z" in the "custom" ...
- 189
1
vote
1
answer
17
views
Input key in dd/mm/yyyy format in google sheets
How to enter a date using dd/mm/yyyy?
For example - I want 25th September, 2022 to be entered in a column, so I have type 09-25. But in Excel, it's possible if I type only 25-09.
Is there any way to ...
2
votes
1
answer
54
views
How to show validation error on linked sheet in Google Sheets?
I have a spreadsheet, with this linking all the subsheets to a "main" sheet.
=QUERY({
IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "custom!A:E")...
- 189
2
votes
1
answer
12
views
Query Formula and Multiple Columns
I tried to use Query function and it was functioning well at first. I initially only had 7 columns. However, when I tried to use to my document with up Column AZ, it is not working anymore.
This was ...
- 21
1
vote
1
answer
20
views
Insert URL inside Google Spreadsheet [escaping characters?]
OK, the big picture is to use all my API Steam results inside Google Spreadsheet so I can create a database of my games and profile.
note: I don't want to share my private STEAMID for security reasons
...
- 111
1
vote
2
answers
12
views
Is there a formula for having a number in one cell be represented as a range in another cell by adding and subtracting numbers to it?
I have a number in one cell, for example 2.6, and in the adjacent cell I would like it shown as 2.1 to 4.6 (maybe displayed as 2.1-4.6 in the cell). This 'range' comes from subtracting 0.5 from the ...
- 11
1
vote
0
answers
11
views
How to wire up ability to click through main spreadsheet cell to source spreadsheet cell, and make main readonly?
I have this configuration:
=QUERY({
IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "custom!A:D");
IMPORTRANGE("https://docs.google.com/spreadsheets/...
- 189
1
vote
1
answer
27
views
Add a constant number to google sheets query result
how do i get this result? i want to get the number one in every row
- 11
1
vote
1
answer
8
views
How to get COUNTIF to validate cell is unique properly in Google Spreadsheets?
Here is my configuration:
And yet here I am getting an error when I shouldn't be. That is, "suffer" only appears once in the entire document, yet it is flagged as invalid:
How do I fix ...
- 189
0
votes
1
answer
12
views
Sytnax for conditional formatting in Google Sheets
I am trying to write a Google sheet to keep track of a certain financial situation.
I have four columns: one for money 'in', the next for profit, the next for loss, then a final outcome column.
In ...
- 1
1
vote
0
answers
20
views
I need a formula to add a row and paste data into the new row in the correct sorted order
I have a google form that someone can use to request a meeting.
These meetings can be for any date in the future and I would like them sorted by the correct date.
Currently I have one sheet 'Sheet1' ...
- 11
1
vote
0
answers
18
views
Google Sheets: How can I make the column dynamic in an indirect formula using the sum formula as well
I would like to use the INDIRECT formula to sum cells B3:B8 and any other rows I might add in the future. However, when I copy the formula the column don't change and it still reference column B.
...
1
vote
2
answers
29
views
In Google Sheets, can you repeatedly autofill until next non-blank cell all at once? (previous value)
Basically, what I want to do is fill all of the blank cell in a column with the value of the previous non-blank cells. For example:
I was wondering if there was a way to do this without having to do ...
- 13
0
votes
1
answer
16
views
Number of Days Positive and Negative Between Two Dates?
On Google Sheets, I am trying to create a formula within a single cell that calculates the positive or negative number of days between two dates. Specifically I am trying to determine how much buffer ...
1
vote
2
answers
19
views
'Large' 'If' Formula Help
I am looking for a formula I can put into Google Sheets that finds the 1st, 2nd, and 3rd largest values in column B, but has to be associated with a number 1 in column A. Problem is, column A does ...
- 11
1
vote
2
answers
29
views
how to check if a special case character is in a google sheet cell?
I working on google sheets files where any special case character (any character other than [a-z,A-Z,0-9] ) is forbidden so to confirm this condition is true I need to check that every character in ...
- 13
1
vote
1
answer
33
views
How can I 'save' a cell order in columns in Google sheets, sort the column to a different order, then reload the earlier cell order?
I'm building a quote for a client, based on their list of tasks.
I've entered these tasks in his order down along my first column (a).
My first row (1) is a collection of headings, which I've frozen.
...
- 113