Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets]

For questions about the Google's web application to create and edit spreadsheets on a web browser. Don't use it for the Google Drive Excel files editor.

2
votes
2answers
48 views

How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
0
votes
0answers
5 views

Google Sheets Conditional Formatting match multiple items in cell (csv)

I have the following data in Google Sheets: A E my my dog great,foo is great paris,dog foo bar bar,car cat I'm trying to do a format rule that highlights a cell, which DOES NOT ...
9
votes
9answers
45k views

How to force a page break in Google Spreadsheets

I want to force a page break after a certain row of a sheet. Is there some analogue of Google Docs’ (and Excel’s) “insert page break” for Spreadsheets? If there is no “native” way to do this, maybe ...
0
votes
0answers
5 views

Conditioning formulas on google sheets

I want to condition all of the cells in A column to change colors whenever the matching cells in B column have the word "later". ex: If column B20 contains later, then I want A20 to change colors. (...
0
votes
0answers
5 views

Color (Highlight) cell only if multiple others filled in

I have a shift sign-up sheet in which I would like the cells in column "A" to be highlighted only once there is a value (name) in the corresponding cells in columns b:d. In this example I would like ...
0
votes
1answer
9 views

How to replace reference cell and have it affect the main cell if it referencing?

I asked a similar question on the Google Support Forum. I was wondering how I could reference a cell with any sort of value in it to another sheet (inside the same Google Sheets document) and then ...
0
votes
1answer
6 views

Conditional formatting cells with multiple conditions and relative references over a range

I have a Google spreadsheet with arbitrary data. Three adjacent columns (let's say A, B and C) have been set up with Data Validation to only allow values from 1 to 10. I'm looking for a way to ...
8
votes
6answers
4k views

How can I get Google Sheets to treat my form-submitted strings as plain text and preserve leading zeros?

I have Google Forms where users enter information, some of which contains leading zeros (e.g. zip codes, ID numbers). However, when this data is saved to the Google Sheet, it doesn't treat it as ...
0
votes
1answer
7 views

QUERY with Week Number

I use query to extract two columns from another sheet with data. Column A has dates Column B - numbers I want to extract only rows that correspond to a specific calendar week number. For example 19. ...
0
votes
0answers
11 views

How to get and use a value of a cell in the PDF file name exporting using that script

Please, how can I get and use a value of a cell in the PDF file name, when exporting/saving using that script: function ExportPDF() { SpreadsheetApp.flush(); var spreadsheet = SpreadsheetApp....
0
votes
1answer
10 views

Convert a text of a specific cell or exclude one cell from all text convertion to UPPERCASE

How to adapt this function to exclude a specific cell or how to apply just to one cell? function onEdit(e) { if (typeof e.value != 'object') { e.range.setValue(e.value.toUpperCase()); } }
0
votes
1answer
10 views

How to count cell with upper leter in array?

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell. I noticed that task is simple if I can use ...
3
votes
2answers
5k views

How to make a ratio win:total with only integers and least terms

I am trying to find a way to show a simple ratio of wins vs total games. For example: 2 wins on 5 games would give me a ratio of 2:5 5 wins on 15 games: 1:3 8 wins on 18 games: 4:9 As a nice to ...
0
votes
1answer
29 views

Formula not working on Google Sheets #error! Formula Parse Error

I have uploaded an excel spreadsheet that works fine but shows as an error on Google Sheets. =SUM(H12,N12,T12,Z12,AF12,AL12)/INDEX(FREQUENCY((H12,N12,T12,Z12,AF12,AL12),0,2)) Its calculating average ...
0
votes
1answer
4 views

Changing cells formatting based on another cells date

I've been looking and can't quite find the answer I'm looking for. I have a spreadsheet with columns Monday through Saturday with data lining up underneath. I would like the current day to highlight ...
0
votes
1answer
11 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...
1
vote
2answers
49 views
+50

How can I allow results from a Google Sheets 'filter' action to be mutable?

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error. ...
0
votes
1answer
13 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
3
votes
2answers
4k views

What's the formula to select a range excluding a sub-range?

I want to say A1:A60 excluding A20:A30. Is there a minus operator for use with ranges, in Google Sheets? I know that you can take the union of two ranges, like {A1:A19;A31:A60}. I would like to know ...
0
votes
1answer
20 views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
2
votes
1answer
2k views

Bring data from Google Spreadsheet to Google Documents

Is there anyway to read the data from a Google Spreadsheet (which I owh) to another Google Document, something like specifying a data source in MS Word?
0
votes
1answer
8 views

1 plot with several lines with a common X axis but unique X and Y values… is this possible?

I am trying to create a graph which shows several smooth lines that are defined by an X and Y value. the problem is that they have to share the X-axis. Is there a way to efficiently add the other ...
0
votes
1answer
10 views

How do I turn a SQL database downloaded table into a Google Sheets multi-line chart?

I want to turn this table (comes from an SQL database) ...into a line chart in Google Sheets that looks like this
0
votes
1answer
10 views

Ampersand causing issues in Google Sheets Formula

I was trying to use the hyperlink formula to create an automatic e-mail creation, however, when i try to pull an information that has Ampersand in it the formula doesn't end. Note that the formula ...
1
vote
1answer
13 views

Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
0
votes
0answers
9 views

Google sheets sub-chart in chart… possible?

I have 3 different data ranges that I need to plot on a google sheets chart. On the main chart I have different value ranges, thus I'll use a left y-axis and a right y-axis, but the 3rd data range is ...
-1
votes
2answers
4k views

Google Sheets, Why won't importxml work on this sheet?

I have a sheet, upon which I'm trying to use importxml() but it just won't work properly. Until moments ago, not a single one of the formulas on this sheet would load at all, they just hung on "...
0
votes
1answer
17 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
0
votes
1answer
18 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
0
votes
2answers
17 views

Change a cell to a colour when a tick box is checked in another cell

I have a cell with a checkbox. I want another cell which contains a date to go green when the tick box is checked. I presume this is done in conditional formatting but I am struggling with it.
30
votes
6answers
41k views

Hot keys to switch between tabs?

Does anyone know if there´s a shortcut to switch between tabs in Google Spreadsheets? If you use CTRL+PgUp / CTRL+PgDown like in Excel you switch between Chrome or Firefox's tabs instead.
0
votes
1answer
22 views

Can I import data from web store directly to Sheet?

I have a spreadsheet that analyzes data uploaded into a sheet from a .csv file that is downloaded from my store for various operations and reports that I extract from it. I would like to know if ...
1
vote
1answer
16 views

Сopy cells depending on values in another cell

In Google Sheets I try to copy cells B2:B4 depending on values in other cells C2:C4. For example - example.xlsx made in Excel. But stuck and can't find a solution on how to do it in Google Sheets. ...
1
vote
1answer
10 views

Google Sheets Formula Import Range with Indirect

I have this formula and want the reference to Archery to reference Archery which will be in Cell E1. I tried using the indirect formula but can't get it to work with the formatting for Google Sheets. ...
1
vote
1answer
12 views

How do I change the year if I want to use the same spread sheet we used last year?

We have a spreadsheet we use every day. One for each month. In creating new ones for 2019, I can not get the date to stay correct. For instance it says Mon Jan 1 which is 2018, When I type ...
0
votes
1answer
5 views

How to change cell range without deleting it?

In a function, let's say the SUM function, I have a range H25:H37 I want to change this range to H23:H40 using the mouse. The only way I could find is to delete the initial value, then I can drag the ...
1
vote
2answers
30 views

Is there a way to highlight specific words in search results?

Is there a way to highlight particular words in cells that I enter into the search window? Sometimes, working with a huge text we need to find only several words among long text and we have to read ...
2
votes
1answer
2k views

Mail Google sheet as excel attachment by Google Apps Script

I have a Google Spreadsheet with some different sheets. I want to send my Google Spreadsheet as excel attachment to some specific addresses in everyday morning. How can I do this easily? I have heard ...
0
votes
2answers
18 views

How to print selected range of a sheet?

Can someone help me to print a selected range of cells in a current sheet in Google Sheets?
1
vote
1answer
10 views

Query with dropdown not working

In my Google sheet I'm getting an error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: I've added a dropdown so the formula will auto-update depending on the tab ...
0
votes
2answers
259 views

Remove duplicate items from array?

What would be the best way to remove duplicate items from cells in rows? For instance... Category 1 | Item 1 | Item 2 | Item 2 | Item 3 Category 2 | Item 3 | Item 1 | Item 2 | Item 3 ...
0
votes
0answers
9 views

Custom color for every user in Google Sheets

We are using Google Sheets as a to-do list and would love to know if we can connect color to a person (editor) that adds something. We are working with 4 people in the same document and it would be ...
1
vote
1answer
26 views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
1
vote
1answer
952 views

Query returning results into merged cells in Google Sheets

Short Question: I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first ...
0
votes
1answer
10 views

Is there a way I can search some info in Google from Google Sheets?

I have a Google Sheet that contains information of companies and names, and I would like to have their job position. Is there a way I can look for it in Google without doing it manually?
0
votes
0answers
9 views

How do I get responses in Google Forms to automatically be updated when updating the spreadsheet?

I'm in a situation, where I have been given a source of information, with all sorts of people. And with that, I wish to insert all this inside of Google Forms, in the response section. And so I was ...
1
vote
1answer
17 views

Copy Sheet To New Spreadsheet

I want to copy one sheet in the Spreadsheet file into a new Spreadsheet file. The sheet that I want to copy contains formulas, I want to just copy the value to a new Spreadsheet file. I have made the ...
0
votes
1answer
11 views

Funny alignment in Google spreadsheet

This is driving me crazy. I have searched the web for an answer, without success. All results are simple pointers on how to use the formatting on the sheets. This misalignment seems to be something ...
0
votes
1answer
9 views

Google Sheets populate X cells with formulas based on single cell containing value X?

Google sheets dynamically populate cells based on single cell value. I don't know what the term would be called and a bunch of searching hasn't resulted in any lead on what it would be called. What I ...
0
votes
1answer
5 views

How do I make one filter take into consideration the order of other columns?

I'm using the Filter (the one you create with the button in 'menus') to sort my table, but when I click the filter icon in a column's header and select "Sort A→Z", it only applies to that column. ...