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.

Filter by
Sorted by
Tagged with
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 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 ...
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(...
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 ...
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 ...
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",...
-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 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 ...
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 ...
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 ...
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 ...
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 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' ...
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. ...

1
2 3 4 5
166