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,487
questions
0
votes
1answer
12 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
28 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
19 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
28 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
31 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
0answers
16 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
20 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
20 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
18 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
24 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
27 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
13 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
39 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)),...
-1
votes
0answers
18 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
12 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
16 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 ...
0
votes
0answers
19 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
23 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
34 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
23 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
37 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....
1
vote
1answer
17 views
Google Sheets: avoid decimal point when integer?
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
26 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
36 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
24 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
18 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
10 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
12 views
Apply Filter script on multiple sheets
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
26 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
47 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
11 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 ...
0
votes
1answer
10 views
Google Sheets formula
I'm using Google Sheets make a spreadsheet with the name in column a, the company in column b and in column f, I need their email.
I have 3 companies, and they have slightly different email formats. ...
0
votes
1answer
14 views
I have a list of phone numbers entered into Google Spreadsheet in this format: 404-313-2974
How can I convert this entered cell into (404) 313-2974 ?
0
votes
0answers
6 views
Excluding 0 in color scaling
I have a list of numbers that I want to color scale, with the lowest being red and green being highest. But the list sometimes includes 0's, I don't want the 0's to be included in the color scaling ...
0
votes
1answer
10 views
Help with a Google Sheets formula for Inventory Tracking
Wanted to get some help with finding the correct formula to input into a cell in google sheets.
I need to determine when we are getting low on product and when it is recommended to order more.
I ...
0
votes
1answer
11 views
Problem with Nested IF() statement on google sheets
I am trying to create a formula for a monthly expense report using the following nested IF() statement:
=IF(B2="Weekdays", C2*20,
IF(B2="Monthly", C2,
IF(B2="Weekly", C2*4,
IF(B2="Bi-weekly", ...
0
votes
0answers
26 views
How to recall a PDF attachment sent from Google Sheets
How do I retrieve a PDF attachment sent from Google Sheets through Gmail?
0
votes
1answer
13 views
Google Sheets - search multiple columns for specific text
Info added after as response to first comment:
I just realized that the IF(PAGE1!C5="OPTION1" probably wont work, probably because I haven't described the wish good enough.
I will describe with this ...
1
vote
1answer
22 views
Convert ARRAYFORMULA output from 2D to 1D range
tl;dr
How to create an 1D range from a 2D range output of an ARRAYFORMULA.
Intro
I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. ...
0
votes
1answer
13 views
How can I get to the bottom of a Google Sheet
I want to open my Google Sheets and go automatically to the bottom/last line entered as there are over 2000 lines.
I have entered the below text in: tools > script editor, as seen in another post ...
0
votes
0answers
17 views
Is there any way to set different formulas for multiple ranges all at once?
In my question Is there any way to set different values for multiple ranges all at once?, I found out how to set different values across dis-contiguous cells all at once, and we developed a good ...
0
votes
1answer
51 views
How do you interpolate data in a horizontal range that's non-linear?
I've been told what I'm hoping to do is called 'spline interpolation'.
I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal ...
-1
votes
0answers
17 views
How do I get the row number of the last non-zero value from a range in Google sheets?
I have a column of numerical data, say a1:a20. I want to search that range and return either the cell address of the last non-zero, non-empty cell, or if that can't be done, the row number (since I ...
0
votes
1answer
18 views
Change cell colour based on another cell containing text
I am new to Google Sheets and trying to create a custom formula in conditional formatting. Looking to see if someone would be kind enough to help.
The picture below is a sample size of two list of ...
0
votes
1answer
31 views
Copy cell info on account of cell matching specific te
Edited as response to comment:
I had to copy data into a new sheet, which is why it doesnt entirely match your answer.
Here is where Im at now: attached screendump is new.
Hope you can help with ...
0
votes
1answer
28 views
Incorrect and somewhat strange results when using COUNTIFs with Arrayformula
I have three functions responsible for checking if I own a stock (by seeing that the column F is not empty) Then I want to check column AB to see what market the stock is from. In the first case I ...
0
votes
0answers
42 views
How to combine specific hours with fees in Google Sheet (dates + cycles + fees)
I’m trying to prepare a sheet to count fees based on cycles (specific hours) to know how many time I got to do some kind of action.
For example, I got a virtual pet which has maximum 1 500 000 ...
0
votes
1answer
16 views
Google sheets - “Anchor” formula to pivot table data which keeps moving
Ive got a Pivot table in a Google Sheet which summarises some numerical data by groups.
Ive attached a screenshot of the pivot table below.
I want to be able to sum the values of the "grand total" ...
0
votes
0answers
8 views
Scatter Chart with Equal Axis Scale in Google Sheets
My data for X and Y uses the same units.
How can I get a scatter chart to force the same scale (units) for both X and Y axis?
In Matlab this would be called equal axis.
