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. Please consider adding a demo spreadsheet to your question, this will greatly increase the chances to get a satisfactory answer.
5,529
questions
0
votes
1answer
10 views
Goggle sheets linking different sheets
Using Google Drive can I link a whole worksheet from a shared with me file to insert it as a sheet into a workbook that I created in my drive.
I don't want to link cell range values, I want the whole ...
0
votes
1answer
19 views
Send a Google Forms notification to two differenz email addresses based on
How can I send an automatic email notification to two different email addresses depending on whether a woman or a man has filled in the form? Please can someone help me with a script to check this ...
0
votes
1answer
14 views
Sheets date formula help
Im trying to find a way to input a due date associated with a task with a column next to it with check marks that can clicked once the task is complete, if it doesnt get checked by the due date I'd ...
0
votes
1answer
11 views
Google Sheets format a date into a completely different date [duplicate]
I am trying to format a list of dates in google sheets. One of the dates are 20161001, I highlight the cell and go up to Format-> Number -> More Formats -> More Date and Time Formats and choose DD MMM ...
0
votes
0answers
25 views
Google Sheets - Query - Sum and group by multiple columns into single column
I have 4 separate column that represents in and out transactions..
Columns
A, B, C, D
a = transaction out (some dollar amount)
b = account type (checking, savings, credit)
c = transaction in (some ...
0
votes
1answer
18 views
Google Sheets SUMIFS issue
I'm trying to do a SUMIFS where one of the criteria has multiple strings in the same column, but the others are just based upon 1 criteria. As you can probably guess I'm having the issue that only ...
0
votes
1answer
14 views
Increment row position every time a script runs
I am currently storing a value each day using the function below:
function addTrigger() {
ScriptApp.newTrigger("updateCell").timeBased().atHour(13).everyDays(1).create();
}
function updateCell() {
...
0
votes
0answers
22 views
Count and keep numbers updated when item shuffles
my question is:
I've a list of items, let's say "A,B,C,D" in cells A1,A2,A3,A4 and so on.
With a formula, I shuffle those items in a set of 2, so I will get a random couple as A,B or C,D or B,C etc. ...
0
votes
1answer
16 views
How to make arrival time in Google Sheets
Hello I would like to ask how to calculate the arrival time.
For example:
I have 1 cell where I put km which are left for 1 truck.
I want to calculate the current time + the cell where I write the km ...
1
vote
2answers
42 views
Filtering multiple ranges into a single list
I have a very long list of data, the first column of which is the date. I have several date ranges from within that list that I want to pull out and combine in a smaller list so I can analyze the ...
0
votes
3answers
25 views
How to return a blank or dash when one of the cells is left blank?
I am looking to calculate pricing and tax between 3 cells. Though if 1 of the cells is blank, I want the end result cell to be blank.
In this instance I have the: (order total * the tax rate) + the ...
0
votes
0answers
18 views
Reject an input in a cell based on some value
I tried to create a roster sheet to my company. Eg. In the Z column there are 3 shifts A,B,C. I put a strength count below such that when an employee mark L instead of A or B or C , the strength get ...
0
votes
2answers
65 views
Google Sheets count consecutive dates
I have a Google Sheet with a list of dates & times like this:
10/24/2019 4:25:00
10/29/2019 4:33:22
10/30/2019 4:33:11
10/31/2019 4:24:11
11/1/2019 4:36:11
11/2/2019 5:21:37
11/4/2019 5:30:20
11/...
0
votes
1answer
9 views
Change currency formatting of cell with dropdown list
Next week I am going to travel to Europe and I wanted to have in a spreadsheet all my spendings, but as I will need to use both Euros and British Pounds, I need to have a way to set the currency used ...
0
votes
0answers
25 views
Splitting multi-line cells with formulas - syntax problems
I have a data set, that is constantly updated/changing, that I would like to have one column's data separated into multiple rows (and copying adjacent columns to the rows);
I tried to use a script ...
-1
votes
1answer
24 views
White pages while printing
I've made a simple sheet to generate a random list of words to be printed. I don't know why but even if I've made the formula to check the number of words in column A of "lista" tab, on the print tab, ...
0
votes
1answer
15 views
Keyboard shortcut to switch sheets in Google Sheets
Is there a keyboard shortcut for switching sheets in Google Sheets? If so, what is it?
0
votes
1answer
16 views
highlight if value is duplicate and corresponding cell of all other duplicates is blank
I have a list of clients who contacted us. We need to get back to them.
I would like to format if:
They appear twice in the list (got that) and
We have not gotten back to them in any of their ...
0
votes
1answer
33 views
Ways to iterate and append a date calculation a set number of times in Google Sheets
Let's say I have a sheet with this dataset:
Start Date Number of Days
3/1/2019 2
From this input set, I want to make the following transformation:
Date
3/1/2019
3/2/2019
3/3/2019
A ...
0
votes
1answer
33 views
Importrange a formula?
My question is:
I've a lot of files, like 30+, that I need to maintain periodically.
My idea was to create a master file, where I will modify formulas only 1 time, and then the formulas will be ...
0
votes
1answer
45 views
Google Sheets checkbox validation “If this then that”
My sheet has two checkboxes who we will call "A" and "B". I need it so A must be checked in order for B to be checked. I cant find out how to do this using data validation but it seems there must be ...
0
votes
0answers
37 views
Google Sheets trying to combine and un-combine text join
Unfortunatly due to security I cannot share the page, but I am using the following codes:
=ARRAY_CONSTRAIN(
ARRAYFORMULA(iferror(
TEXTJOIN(",",TRUE,unique(IF(($D4=Section!C$2:AW$2),Section!C$3:AW$4,"...
-1
votes
1answer
27 views
Query matching two columns to three columns
I have 5 Columns divided in two groups.
The first column of the first contains the name of a fruit (Pear, apple or peach) and the second its price (1 -> 5). In the other group, I also have the name ...
2
votes
1answer
35 views
Increment Cell Hotkey
Is there a hotkey in google sheets to increment any cell +1 or -1?
I've seen many guides on click-drag patterns, creating buttons to increment a specific cell, but sometimes if you're doing inventory,...
0
votes
0answers
31 views
A script in Google Sheet to calculate the delay in attendance based on fixed time
I need a script to calculate the attendance for each employee based on his shift time. It must deduct the total delay or any break in the middle of the shift.
For example, A shift time is between 8am ...
2
votes
1answer
32 views
Extract a number with a letter
I am trying to extract a number and add them in a range of cells if they are attached to a certain letter.
Ex: Cell B1=5C, Cell C1=3C, Cell D1=2B Looking to extract and add all cells with the letter ...
1
vote
1answer
27 views
Concat text from one column with text in non-empty cells in range
I need a formula to concatenate the text from column A with each non-empty cell in B1:Z. The number of rows in the input data will change, so that has to be accounted for dynamically. Also, the rows ...
1
vote
1answer
29 views
Google Sheet hide emtpy cells
So I have a simple formula I use for my tabletop game.
I have Player names in A2-A26 and their turn number in B2-B26
In E2 I have the formula
=Sort(A2:B26,B2:B26,False)
Now this makes a list of ...
2
votes
1answer
48 views
Google Sheets - How to reduce number of X axis labels?
I have a chart with dates along the X axis, but the chart displays them quite close together, producing a rather ugly graph. How might I reduce the number of visible labels to increase the readability ...
1
vote
1answer
46 views
Sheets-select range of cells relative to current cell
I am trying to calculate the sum of a range of cells relative to the current cell. This is what I have, and it works:
=SUM(INDIRECT(ADDRESS(ROW()-6,COLUMN()-1)), INDIRECT(ADDRESS(ROW()-5,COLUMN()-1)),...
0
votes
1answer
41 views
Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets
Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?
The spreadsheet I'm trying to do this on ...
0
votes
0answers
25 views
How to make a SPARKLINE with text (data label) in Google Sheets
I want to create a =SPARKLINE() with the parameters {"charttype", "bar"} with a text in the same cell as the chart. How is it possible?
0
votes
1answer
18 views
How would I define multiple answers based on which checkbox is selected in google sheets?
I currently have this formula set into my cell:
=if(G32 = TRUE, "12", "")
Thus, if G32 is checked it will display 12. However, I want to allow the formula to react with multiple check boxes each ...
-1
votes
1answer
35 views
UNIQUE ,INDEX AND IMPORTRANGE
I am stuck. I use the import range to get a set of data however now I need to the unique values that that matches the import range that I have.
Say for example below is randome sample. I have import ...
0
votes
1answer
37 views
Change Google Sheet cell background color based on hex value within
So I've been looking online and I am not able to find an answer that is working for me. I have a Google Sheet, and some of the cells within that sheet have hex values in them. What I am trying to do ...
1
vote
2answers
38 views
Find and place in a column, the first non-blank cell in each row from a range of columns
I'm attempting to create an ARRAYFORMULA that can extract the first non-blank cell value on each row in a range of columns.
My latest attempt is this:
=TRANSPOSE(QUERY(TRANSPOSE( A2:D6),"select * ...
0
votes
0answers
30 views
Google Spreadsheet cell value dependant on other cell value
I am trying to make on cell value dependent on another cell value in the same row. I have a Project List spreadsheet with Data Validation list in column B with several Project Status (IE: Urgent, ...
0
votes
2answers
38 views
Average of only the numeric part of cell values when some of the cells contain text and numeric values
I am trying to figure out a formula that will allow me to find the average of a group of cells, where some of the cells only contain numeric values, and some cells contain both numeric values and text....
2
votes
3answers
38 views
Google Sheets: avoid decimal point when integer - [Bug discovered?]
Is there a way to use a custom number format in Google Sheets so that decimal point will be visible only when decimals are present?
For instance
9
8,2
10
Is there a newer solution about achieving ...
1
vote
1answer
31 views
Create list of cell addresses contained in a range
I'm looking for a single arrayformula to convert a specified range, such as B2:D4, into a list of addresses (not values!) contained in that range, in this case B2,B3,B4,C2,C3,C4,D2,D3,D4.
Attempts ...
1
vote
1answer
39 views
Count query based on multiple conditions
I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.
For reference, when our guild goes through dungeons, we ...
1
vote
1answer
25 views
Make cell range appear in different position to VLOOKUP?
I want to use VLOOKUP to search the highest value in a given range, and report to me what day it's the highest.
Example VLOOKUP for my actual data set in case if it matters:
=VLOOKUP(MAX(A3:A1001),...
1
vote
1answer
22 views
Google Sheets, CountIf, name matches and is within date range from another spreadsheet [duplicate]
In another sheet I want to count how many meetings were scheduled for a rep within a date range.
My 3 columns are the date, rep, and misc data
=countifs(
"https://docs.google.com/spreadsheets/d/...
0
votes
0answers
13 views
Google Sheets: Stacked bar chart showing event totals by month
I have a Google Sheets spreadsheet that tracks four types of events on a daily basis; we'll call them A, B, C, and D. More than one event can happen on a day; if this happens there are two (or more) ...
0
votes
0answers
16 views
Apply Filter script on multiple sheets [duplicate]
I made a script to filter column D3 on active sheet, I would like to do the same on multiple sheet at the same time from an action button on my index page.
E.g. (tab1, tab2, tab5, tab7)
What do I ...
0
votes
0answers
23 views
Appscript for UPPER function
I have this script that works perfectly:
function firstColumnUPPER() {
var activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();
if (activeCell.getColumn() == 2) {
if (activeCell....
0
votes
0answers
43 views
Import Price from Amazon into Google Sheets
I am attempting to pull a price from Amazon.com into google sheets using IMPORTXML() as per this answered question, but so far no luck - I get "Imported content is empty."
=IMPORTXML("https://www....
2
votes
3answers
51 views
Find and extract words/names from a string beginning with specific character
I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell:
specifically, I want to extract words/names that begin with ...
0
votes
1answer
10 views
Add year (multiple different years) in cells that have only day and month (Google Sheets)
I have a spreadsheet as follows in column B:
10/11
27/10
05/05
01/01
24/12
11/07
03/03
27/11
15/10
The top ones are always from the year that we are, in this example it's 2019, but according to what ...
0
votes
1answer
14 views
Googel Sheets MINUS formula is adding
I have been working with some numbers in Google Spreadsheets and when I type the formula =MINUS(A1,A2) or even =A1-A2 the spreadsheet keeps adding the two numbers instead of subtracting them. What am ...
