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.

Filter by
Sorted by
Tagged with
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 ...