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
0answers
2 views

How to customise ordering of segments in stacked waterfall chart using Google Sheets

I've drafted a stacked waterfall chart in Google Sheets (shown below). By default, the ordering of stack segments are as follows: In Column A: Apples, Bananas, Carrots, Durian from top to bottom In ...
1
vote
1answer
589 views

Query based on column header in Google Sheets

I am trying to automate Worship list using Google Sheets, so it works good but one thing I can't do can be more than useful. I have a sheet like this: in row 1 dates and below songs used that Sunday....
0
votes
0answers
2 views

Unable to position custom data labels in center in Google Sheets waterfall chart

Context I've drafted a stacked waterfall chart in Google Sheets, with custom data labels (guided by Ben Collin's tutorial on custom data labels). My goal is to have the custom data label positioned ...
1
vote
1answer
10k views

Cell last modified date & user stamp Google Sheets?

I want the cells of two columns in my Spreadsheet to auto-update with the date (m/d/y) and name of user when cells in a certain range are edited. For instance, Catherine goes to sheet on 3/6/2017 (...
0
votes
0answers
3 views

Google Sheets Data Feed has extra column headers in the JSON

I have a client who is using Google Sheets as a data feed. When using the correctly structured URL for the sheet, I see blank columns in the target table. When I look in the code inspector, I see gsx ...
0
votes
1answer
139 views

How can I use GOOGLEFINANCE to return a share price in a certain currency?

I have shares in different currencies that I want to track in only a single currency. I currently have a cell that returns the currency and one that returns the price, I then multiply them. However, I'...
0
votes
1answer
12 views

Countifs in between ranges

I'm struggling to pull below conditions together. I'd like to have a formula that will count values between 1 and 5 only when cell U13 contains the text 1-5. =COUNTIFS(U13,"1-5") =COUNTIFS(K2:K22,"&...
0
votes
2answers
1k views

Google Sheets registering blank cell as zero

I am writing a formula into google sheets and I do not want the formula to register a blank cell as zero. The formula is: =ARRAYFORMULA(MIN(IF(A:A=H12, (D:D-C:C)))) It keeps giving me a zero value ...
0
votes
2answers
49 views

Function to search columns and tally data

I am wondering if I can write a function to search a column in my spreadsheet for a specific name, then return x number of values within the row(s) that contain the name. For example... M4 holds the ...
0
votes
1answer
2k views

Copying filtered data in Google Sheets

What is the correct method for copying data in a filtered view in Google Sheets? For example, in the image below, I want to copy the cells with values 1 & 3 and paste them into the cells with ...
0
votes
1answer
19 views

Unsimplified Fractions in Google Sheets

How can I get Google Sheets to display fractions in the form I enter them, rather than in a simplified form? I'm trying to evaluate how a student did on a test, so I have one cell that reports the ...
0
votes
2answers
21 views

Help with a Vlookup function to return multiple results of not exact match

I am trying to run the following code to return all the matches of column A from column C in a set of rows. In this situation, cell A1, is contained in a few iterations of column C, however, these are ...
0
votes
1answer
2k views

Horizontal sort in Google Sheets

I am a teacher and I store data from assessments in a Google Sheet. In the rows, I have student names and in the columns, the score they received from each question. I want to be able to sort by row (...
0
votes
0answers
8 views

Match based on row with lowest value

I'm finding the index of a row using another text value in a row as a reference to add a value to a "unit cost" column: =INDIRECT("Sources!I"&MATCH(A5, Sources!A:A, 0))*CONVERT(C5,D5,INDIRECT("...
0
votes
1answer
19 views

Aggregate data with Sheets pivot table or graph

Please have a look at this google sheet here. It has a pivot table with 3 columns: A period (for example: 2016/01), a category and a number. I would like to do a chart that looks like this: For ...
1
vote
1answer
648 views

Conditional Formatting across a range of cells with a formula in Google Sheets

I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018". I have a separate sheet with a data set that has rows that classes end such as "9/24/2018". I want to ...
0
votes
1answer
22 views

Google sheets spreadsheet will not format date correctly

I have a Google spreadsheet with a date column formatted to dd mmm yyyy. Many of the dates are formatted this way, but some cells will not format this way, and no matter what I select under "Format", "...
0
votes
1answer
1k views

How do I allow a user to edit a specific column only?

I have a protected range but I want to do the opposite of its function. I only want a user to be edit that specific range. So, how can I have them "view only" for the whole spreadsheet but edit one ...
0
votes
2answers
6k views

How do I combine two different types of charts to compare two types of data?

I am trying to plot two different types of data: segmented volumes and rating. In the linked example data sets & charts you will find two different charts that I am trying to combine. The first ...
0
votes
0answers
7 views

From Google Form to sheet to doc

I have made a form to sign people up for an outdoor club. Info includes their name, address, equipment they have etc. As the activity is a group activity and people will be meeting from all over the ...
1
vote
1answer
18 views

When I insert a row, indirect does not update

I have a summary sheet and I would like that every time I add a new sheet everything gets referenced to the summary sheet. I have used indirect to do this, the only flaw is that once I add a row, the ...
7
votes
3answers
21k views

2-axis line chart in Google Sheets

I've set up a Google Sheets to compare one set of statistics against another. The statistics are for traffic vs. an action. The actions only happen once a day and not every day whereas the traffic has ...
-1
votes
2answers
15 views

How to archive cell changes in new tab?

I have a Google sheet that I'm collecting some statistics in. There is a particular cell that I'd like to track changes in a separate tab. I'm looking for a function to copy cell if the cell data ...
3
votes
1answer
715 views

Simultaneously sort multiple ranges on a single sheet in varying orders

I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
-1
votes
2answers
30 views

Empty cell reference when “Loading…”

I want to do a calculation with imported data from an HTML link. The data is imported every minute. There is loading time and this ensures that the cells are empty during loading. Because my formula ...
1
vote
1answer
633 views

How to calculate time worked in Google Sheets after 12am?

I usually work from 3pm to 1am. I can't seem to find out how to make google sheets find the correct duration between those times. I know the correct duration is 10 hours, but no matter what I do ...
0
votes
1answer
193 views

Formula for custom number format in Google Sheets (e.g. 5M --> 5,000,000; 50k --> 50,000; etc)

I've been trying to figure this out for a bit but everything I've tried doesn't work. A quick search brought me to the conversion from full number format to condensed format (5,000,000 --> 5M; 50,000 -...
-1
votes
0answers
9 views

How do i implement importrange the formula below?

can anybody here share with me how to actually implement importrange into this formula.. =SUMPRODUCT(SUMIF(INDIRECT("HON_"&Parameters!$B$7&"_"&YEAR(Parameters!$A$2)&"-"&MONTH(...
0
votes
0answers
10 views

Using Google Forms to create an automatically updating inventory

I have created a Google Form that is used for people in the office to request items (swag given to donors). After an item or items are requested, the request must be fulfilled by a team member. I ...
0
votes
1answer
72 views

Concatenating URL with a value to create link, but links only partially work

I have a column of cells in Google spreadsheet with values such as: b2397 b9504 b3333 b8655 bxxxx I would like to combine each value with a URL, for example: http://websitex.com?id= ...
0
votes
0answers
16 views

Race results as race is being run

i have a sheet for a multi lap race. At any point I want to print the current standings. Racer D is currently leading having gone the furthest, racer B in 2nd, and so on. How can I get the current ...
0
votes
1answer
21 views

Average value using different sheets on Google Sheets

I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, ...
5
votes
6answers
65k views

How to add bulleted list in Google Sheets

I am unable to add bullet in front of any sentence. See below example, I need circle bullets in front of every sentence written in one cell. First Sentence Second Sentence Third Sentence How can I ...
0
votes
1answer
125 views

Creating a Dashboard in Google Sheets which multiple people use issue with multiple users

this may be open-ended but let me put this forward. I have a very complex dashboard which works out staff hours across the country. In this dashboard our team members have a drop down to choose their ...
0
votes
1answer
15 views

google sheets query function

google sheets query function Trying to use the following query: =query(WABrdg729!A:B, "select A, B where A > date '"&text(datevalue($A$6), "yyyy-mm-dd")&"' AND A > timeofday '"&text(...
0
votes
1answer
207 views

Concatenate using an array formula to result in last name.firstname

We have a form that asks for the students first name and last name. I am trying to using concatenate and an array formula to display "Lastname.Firstname". I used: =ARRAYFORMULA(CONCATENATE($B$52:$B,...
0
votes
0answers
7 views

Google Sheets ImportHTML formula not pulling table I need [duplicate]

I have a Google Sheet trying to pull website table using ImportHTML formula to get a table from https://mesowest.utah.edu/cgi-bin/droman/raws_ca_monitor.cgi?state=AZ&rawsflag=3 ImportHTML(https:...
0
votes
1answer
10 views

Process visualization based on data

I am trying to use a Google spreadsheet to visualize a customer journey. I am trying to create the following graphic: I have created the following example spreadsheet: https://docs.google.com/...
0
votes
1answer
67 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
0
votes
1answer
57 views

Add row to Google Sheets and keep formulas from previous row

I want to add a row to my spreadsheet but keep the formulas – and only the formulas – of the previous row. The #1 solution provided here doesn't help me because it also copies non-formula information, ...
0
votes
1answer
344 views

Move rows on 2 sheets older than 24 hours to archive

I have been trying to figure this one out for a while now and would really appreciate some assistance. I have a Google Sheet that is being updated by a Google Form. Due to this I needed to mirror the ...
0
votes
2answers
28 views

Chronological ordering of checkbox in Google Forms

I have a checkbox in my Google Form, which sends the user selections in the form in an ascending order list: But I want to grab the list in the order of selection, for example if the user clicks in ...
0
votes
1answer
186 views

Are permissions granted by IMPORTRAGE bi-directional or one-way in Google Sheets?

If I want to import "Sheet A" from one workbook into "Sheet B" in another workbook by using =IMPORTRANGE() inside of Sheet B - when I grant it access to Sheet A is that permissions only 1 way? Meaning ...
2
votes
1answer
13 views

Conditional Formating for Google Sheet comparing to list of text (highlight duplicates)

I am trying to create a custom formula for conditional formatting. I want to highlight the cell if the text appears in a list of names. This list will contain 210 unique names so I can't type each one ...
1
vote
1answer
14 views

How to countif with dates before today's date that auto updates

I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior ...
1
vote
3answers
9k views

Formulas in Google Sheets won't execute

I can't seem to get formulas to execute in a Google spreadsheet. Whenever I input a formula, it does not return any output, but rather leaves the cell blank. I have tried the following troubleshooting:...
0
votes
0answers
10 views

Select Column A Where values in Column A are not present in Columns D-G

Having trouble with a query. I want the query to select column A where values in Column A are not present in a Columns D-G. I have attempted a filter and had no success.
3
votes
2answers
14k views

How do I share a Google Sheet with 'read only' permissions?

Given some Google Sheet I'm aware of the Share button in the top right corner. However, the default settings allow anyone with the share url to edit the sheet. Is it possible to set permissions so ...
0
votes
1answer
12 views

Conditional Formatting in Google Sheets based on the same rows conditional format

I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
7
votes
4answers
17k views

How to stop NOW function from auto-updating?

I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...