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,781 questions
0
votes
0answers
3 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
10 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
23 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
13 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.
...
0
votes
1answer
7 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
24 views
How to return values in a formula, from rows of latest 4 non consecutive dates recorded
Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of non-consecutive dates.
=ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0),
{...
0
votes
1answer
4 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
16 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
5 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
16 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
10 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
16 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
17 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
16 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
13 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
17 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.
2
votes
2answers
41 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♥ ...
0
votes
1answer
24 views
Get the name and link file based on Folder ID
I created a script to get the name and link file based on Folder ID. The file name and link are displayed on sheet 'Get_Link'.
File spreadsheet
The script that I wrote like this:
function ...
0
votes
0answers
17 views
Picking a Total from the Current Month
I have this working formula (below)
=SUM(QUERY(QUERY({{A2:B},{ARRAYFORMULA(IF(LEN(A2:A), WEEKNUM(A2:A, 16), ))}},
"select *", 0),
"select Col2
where Col3 matches '"&WEEKNUM(TODAY(), 16)&...
1
vote
2answers
17 views
Reverse subtraction if less than zero (negative)
I have a simple Google spreadsheet that Takes the estimated cost for a service provided and compares it to the actual cost of the service. the equation I have in the Q column is =K4-P4. When the ...
0
votes
1answer
15 views
How can I use ARRAYFORMULA (or something similar) with GOOGLETRANSLATE?
I'm learning a new language and I recently discovered Google Sheets' GOOGLETRANSLATE function. I thought it'd be neat to have a personal dictionary of translations which I can even annotate. So I ...
0
votes
1answer
16 views
Running script to replace “null” with nothing in Google Sheets
I tried to do this through a macro but I'm unable to have it actually record. I'm not a coder so I've been recording macros and using the code from there to create an add on. I need a script that can ...
1
vote
0answers
17 views
Is there a way to create a calculated field using results of another calculated field?
Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
0
votes
1answer
18 views
How to trigger a script in Google sheets when a value changes
I try to fire a script when a value in a certain cell or range of cells changes.
(it's not changed by the user, so I can't use onEdit)
I've tried to use onChange, but this doesn't fire my script at ...
0
votes
1answer
14 views
How to import/merge data from from 2 separate sheets from another spreadsheet
I need to import data from "Spreadsheet A" to "Spreadsheet B" but all the data is in two separate sheets "MY" and "NY" in "Spreadsheet A"
I basically need exactly this but with a function to import ...
1
vote
1answer
18 views
How can I have Google Sheets automatically parse and calculate text that follows a certain pattern?
I have the following sheet:
https://docs.google.com/spreadsheets/d/1iEMjWVEwyuqbn_imW1UZ-AF2NlE__IbRZdHQifKJjko/edit?usp=sharing
I would like to calculate "Total Work" by deriving it from my own ...
0
votes
1answer
14 views
In Google Sheets, Enter key moves selection
In Google Sheets, pressing the Enter key moves the cursor down a cell. For example:
position cursor on the cell
type to enter text
press Enter key
Expect: Cursor stays on the cell I was just ...
0
votes
0answers
11 views
Build a value-over-time line chart from monthly values?
I have a spreadsheet that looks like this:
+---------------------------------------+
| |01|02|03|04|05|06|07|08|09|10|11|12|
+---------------------------------------+
|'16| ?| ?| ?| 1| 2| ?| ?| ?| ...
1
vote
1answer
27 views
Trying to Query a list against another list
First time ever using stack exchange so please excuse any formatting problems.
I'm trying to create a sheet where Sheet1 is a dump of data and Sheet2 contains:
A date range
A list of items
True/...
0
votes
2answers
21 views
Add columns based on rows
I'm currently working on setting up a sheet, but I want to add columns in one worksheet based on rows in another worksheet.
I have no idea if this is even possible, I've tried googling it. Might be ...
0
votes
0answers
17 views
Script to copy a sheet and create a new sheet from a form submission [on hold]
The goal is to have the process go -
Form Submission
Data goes in to a "main data" Google sheet (This is done automatically)
(This is where I need help creating the script)
A copy is made of an ...
0
votes
1answer
17 views
Sort Google Spreadsheet by 'alternating' values in multiple columns
How can I sort a random list in Google Sheets so it returns alternating values? Is it possible to do it with multiple columns?
How can I alternate so each Room starting with the lowest has one ...
1
vote
0answers
14 views
Google Sheets Clock Cycle Counter - Increment value only on TRUE value in another cell
I want to increase the numeric value of a cell output, based on the contents of another cell input that will contain a binary value. I'd like to do this without using scripts.
Cell B5 is going to ...
