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.
4,797 questions
0
votes
1answer
4 views
SUMPRODUCT inside ArrayFormula
I have a fixed vector, say $A$1:$E$1
I also have a vector per row, say A2:E2, A3:E3, A4:E4...
Now I want output in Column F as
F2 = sumproduct(A1:E1,A2:E2)
F3 = sumproduct(A1:E1,A3:E3)
F4 = ...
0
votes
0answers
7 views
Google Sheet use filters to sort with Unique
In google sheet, I have a data set A:
In another sheet I am creating a dynamic list using the UNIQUE formula:
However, when I use filters to sort the data, it gets all scattered leaving empty cells ...
0
votes
1answer
10 views
Conditional formatting formulas in Google Sheets
I want to use Conditional formatting so all of the cells in column A change colors whenever the matching cells in column B have the word "later".
ex: If cell B20 contains "later", then I want A20 to ...
1
vote
2answers
9 views
Conditional Highlight: color change 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 ...
1
vote
1answer
10 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 ...
0
votes
1answer
9 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
14 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....
1
vote
1answer
11 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 ...
0
votes
1answer
13 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 ...
0
votes
1answer
21 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, ...
0
votes
1answer
7 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
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
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 ...
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 ...
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
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 ...
0
votes
2answers
19 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.
0
votes
1answer
30 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 ...
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
13 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.
...
2
votes
2answers
52 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
1answer
6 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 ...
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
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 ...
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
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
20 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
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.
...
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
17 views
Create 'bookmarks' in google sheets [on hold]
I'm pushing the edge of google sheets, doing with a spreadsheet what ought to be done in a database.
As an example: Cust_Orders is a sheet broken into blocks of 15 rows. Each block is one order ...
0
votes
2answers
13 views
How to use IMPORTRANGE when I have multiple tabs?
I have to export some data from a Sheet with 30 tabs(a tab for each day of the months) and I have to extract data under specific criteria from all the sheets which have to be done every month on a new ...
1
vote
1answer
6 views
Set certain days of the week on repeat [on hold]
I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
1answer
8 views
Query Function Not Putting Consolidated Data in the Right Place
So I don't know how to explain this completely, but basically, I'm using the query function to consolidate data from multiple sheets into one Master Sheet. The function itself seems to be working fine ...
1
vote
1answer
11 views
Rotate and duplicate row values in Google Sheets
I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:
a | b | c | d
would become:
a | b
-----
a | c
-----
a | d
Is there any way to do this ...
0
votes
1answer
18 views
Sending pdf with Google sheet email
Trying to send a pdf attachment to an email using Google sheets. I have checked and tried to use the replies in previous responses but my code fails after sending the first email.
This is the code I ...
0
votes
0answers
22 views
Google Sheets Script - check if value exists before copy-paste
Can someone help please as I am new to Google Scripts.
I have this simple code that copies values from one sheet to another
function copyrevision() {
var ss = SpreadsheetApp.getActiveSpreadsheet ()...
0
votes
0answers
24 views
Change default zoom on a Google Sheet?
I use a shared Google sheet to display apparatus issues for a fire department. This is displayed on a tv using an Asus Chromebit. Every time someone logs into the sheet it resets the zoom to 100% ...
-1
votes
1answer
13 views
Why doesn't google forms recognize responses manually added to the responses sheet
I'm trying to see if I can add responses to a sheet manually through a proprietary application but whatever response I add to the sheets document is pushed down and isn't recorded on the responses ...
0
votes
1answer
16 views
Joining Two Working Formula's
I have the following working formula
Located in K2 =IF(INDEX(Details!B18:B81,MATCH(C2,C2,0))<>C2,S30,"0")
So I want it to only use the number in S30 if the date in C2 is not in a range of dates ...
1
vote
1answer
14 views
How can I track the time it takes for someone to fill out a Google form?
I am running a study for my thesis, and would very much like to track the time it takes to complete a Google form to compare against how long it takes to fill.
0
votes
1answer
8 views
How to pull data from a random cell on page load in Google Sheets?
Basically, I'm just trying to figure out how to make a cell pull a random cell's value within a range at random when the page loads.
For example, have cell A1 pull a cell at random from range B1:B20 ...
2
votes
1answer
14 views
Dot leaders in Google sheets
Dot leaders are repeated dots that visually connect a label to the corresponding information on the same line, commonly used in tables of contents but also used in accounting formats, for example:
...
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?
0
votes
0answers
7 views
Auto Increment a Named Range in Google Sheets
I am working on a bidding calculator and would like to be able to automatically increment a Name Range once I finalize the formulas once. For instance, there are 15 cells per item row with formulas ...
0
votes
1answer
6 views
How to paste a comma separated string into one column in Google Sheets?
For example, if I have (bob, china, bing, bong). I want to put bob on top of china on top of bing on top of bong.
1
vote
2answers
53 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
12 views
Filter column based in other column (using query prefer)
I am trying to make a filter with a query based in a column, but I cannot make it works.
https://docs.google.com/spreadsheets/d/1zKMW8vuyHRpS8eVEmRgAOg_dBQ0r2OIXnqV3_TFEm9o/edit?usp=sharing
This is ...
1
vote
1answer
18 views
Is there a formula to list all possible 2 card combinations of 4 playing cards in Google Sheets?
Example:
Is there a formula to list all possible 2 card combinations of 4 playing cards?
4 playing cards:
A♥
A♦
A♣
A♠
The result should be 6 combinations only (Not 4 x 4 = 16 Combinations):
1: A♥ ...
