Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

2
votes
1answer
219 views

Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet with a keyword instead of exact match

I would like to use the importrange formula to import data from "spreadsheetA" that contains "keyword" in columnD. I can get importrange to work but I cannot figure out how to filter it so it will ...
0
votes
1answer
3 views

How to select existing sheet to be populated by a Google form?

I have a team working in pan India with 6 Team leaders. I have created a Google form to collect the everyday status report. Now the question is: How can I assign a particular sheet[already created in ...
0
votes
1answer
4 views

How to import historical open price in Google Sheets with GOOGLEFINANCE?

For some reason, I can't get the google finance function to work with any open-price lookups I do. For example: =GOOGLEFINANCE("AAPL","priceopen", DATE(2019,4,12)) comes back as #N/A with message: ...
0
votes
1answer
3 views

How to use a cell value to define the sheet on IMPORTRANGE

Trying to create a spreadsheet that reads values from a certain sheet of another file using importrange formula. I am trying that the referenced external cell be a cell on the current one so I could ...
0
votes
0answers
3 views

Copy data generated by QUERY

I want to copy the values generated by a complex scripted query to another sheet, for users to work on without deleting the original query. The query generates a variable range of data, depending on ...
0
votes
1answer
34 views

Arrayformula for running MAX of separate column

I'm trying to use ARRAYFORMULA in Google Sheets to get the max figures from a separate column only up to the row being calculated each time but can't for the life of me figure out how. It sounds more ...
0
votes
1answer
65 views

Fetch Random String From Array For Each New Form Entry

!Sheet1 is populated with entries coming from a Google form !Sheet2 has an array of 12 employee names in array A2:A13 How can I automatically populate the cell on the right column of every new line ...
1
vote
1answer
614 views

How to set Monday as start day of the week in Google Sheets date picker

How to set Monday as start day of the week in Google Sheets date picker? I have changed the locale, changed start day of the week in Google Calendar and can't find a way to solve this.
0
votes
1answer
15 views

How to increase formula by increment of 10

I have a spreadsheet with one value per year. I would like to take the average of decades (with, obviously, 10 cells per decade), so I set up a formula along the lines of =AVERAGE(A(1+10):(A(10+10), ...
1
vote
2answers
237 views

Using REGEXREPLACE() and VLOOKUP() to search and replace using a pairwise list

Suppose I have a list of people with unique first and last names, in two columns, B and C. See example spreadsheet here. Cell E6 contains the following text: Firstname1 would like to say hello to ...
1
vote
2answers
29 views

How to keep a checkbox value to reuse it later in Google Sheets

I'm working on a Google sheet actually (only for fun) and I'm actually stuck on something, as I said in the title, I want to keep a checkbox value in Google sheet. I will explain my problem. I'm ...
0
votes
1answer
14 views

Using a cell value, to dynamically create a formula, that links to a cell in a different sheet (tab)

Ok. This is a bit complicated, but I will do my best to describe the problem. All of these elements are located in multiple Sheet pages (Tabs) within one singular Google Sheet (Workbook). Sheet 1: ...
0
votes
1answer
13 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
5
votes
1answer
9k views

How do I multiply column values?

I simply want to multiply column h with column j, and store the result in column k in each row. What do I type in as the formula to do this?
0
votes
2answers
16 views

How to work over rows from all other sheets in Google Sheets?

I have a 'summary' sheet and 12 other sheets named 'Jan20', 'Feb20', etc. In the summary sheet I have this formula (to calculate Uber costs): =FILTER('Jan20'!F2:F, REGEXMATCH('Jan20'!D2:D, "UBER")) ...
0
votes
2answers
21 views

How to export CSV without commas in unpopulated cells?

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website. However, when I go to export, it seems ...
1
vote
1answer
361 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....
1
vote
1answer
9k 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
1answer
28 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
2answers
18 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
19 views

Grabbing only non-empty cells from a row range for every row

I have a survey that sends responses into a Google sheet. The raw data that is inserted is messy, so I have another sheet that processes the incoming raw data. One of the things I need to do is: from ...
0
votes
1answer
13 views

DAY function does not work as expected in google sheet

I have a issue with Day function; I am extracting date from text string and storing it in a column; say Sep 30, or Sep 23; I want to see DAY of this date and put it in next column; something like ...
1
vote
3answers
22 views

Given a shared Dropbox link to an Excel file, how can I easily open it in Google Sheets?

Scenario: I get an email every two weeks with a vendor's price list as a link to an Excel file in their Dropbox. At this point, I have to: open the link — This takes me to Dropbox, where the file ...
0
votes
1answer
950 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
2answers
236 views

Remove duplicate items from array?

What would be the best way to remove duplicate items from cells in rows? For instance... Category 1 | Item 1 | Item 2 | Item 2 | Item 3 Category 2 | Item 3 | Item 1 | Item 2 | Item 3 ...
0
votes
1answer
244 views

Split multi-line (comma-separated) cell into new rows + duplicate surrounding row entries

Based on: Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries) How can I rewrite this for use of a comma as separator and remove blank spaces? Input sheet: ...
1
vote
1answer
18 views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
1
vote
1answer
508 views

Compare two lists and produce a list of names not duplicated

I have one list of total available people and the second list of people who have been assigned. I would like to auto-populate a third list of the people (from the first list) who have not been ...
1
vote
2answers
19 views

Google Sheets monthly rolling average

I have a table in Google Sheets with data for different people by month. I want to display a monthly average that will be progressive as the year goes by. So they can have an idea of how they 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
1answer
17 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
371 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
0answers
7 views

Google Sheets text wrap no longer expands cell

All of a sudden I seem to be having trouble in my Google Sheets auto extending height of cell with text wrap, for certain cells. Mac OS: 10.13.6 (17G65) Chrome: Version 73.0.3683.103 (Official Build)...
1
vote
1answer
12 views

Compile cells when cell contains certain value

In Google Sheets, I have a range of values as follows: I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range ...
0
votes
2answers
13 views

Format cell if all the cells in a row are not empty

I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
0
votes
0answers
13 views

Email Template for Mail merge not working!

function getRowsData(sheet, range, columnHeadersRowIndex) { columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1; var numColumns = range.getEndColumn() - range.getColumn() + 1; ...
0
votes
1answer
9 views

Can I navigate from a pivot table back to the original database?

Once I am "in" a pivot sheet, is there an easy way to click on a row or cell in the Pivot table and navigate directly back to the related data in the original database (Google Sheet), from which the ...
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 ...
3
votes
1answer
652 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
vote
2answers
4k views

List of unique values based on a criterion

I am working on a Google Sheets that has a phrase, assigned number to the phrase and then I want to create a new list on a new column of phrases that have an assigned number less than 5. Moreover, I ...
1
vote
1answer
368 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
123 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 -...
2
votes
1answer
630 views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
0
votes
0answers
12 views

How do I circumvent protected sheets to expand or collapse groups and rows in Google Sheets?

A budget I am working on has each month on the same sheet and I would like to control which months are visible when comparing expenditure and income. In order to do so, I grouped the rows and columns ...
1
vote
1answer
20 views

How to convert multi-value answers into single column in Google Forms' response sheet?

I have a Google form that asks for Name and up to 3 Score. The data is dumped into a sheet like this But that data is not very convenient. I want to have the data like this... Can I do that by ...
0
votes
1answer
22 views

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

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= ...
1
vote
1answer
11 views

Finding multiple “most common values” in a column

I am trying to display the 5 most commonly appearing text values in a column in Google Sheets. The following will give me the most common value in column A: =ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:...
0
votes
1answer
23 views

Find previous non-blank cell, but without a specified range in Google Sheets

I've come across plenty of formulas to find blank cells within a certain specified range, but can't seem to figure this out / find an answer. I have a spreadsheet kind of like this: . Where column ...
0
votes
1answer
17 views

How do you make the graph relative in Google Spreadsheet?

I have a simple graph in Google Spreadsheet just showing weights from different dates. The problem I have is that the graph is starting from 0, giving it a lot of dead space. I want it to be relative ...
0
votes
0answers
11 views

Trying to make a schedule [on hold]

I am trying to make a schedule and assign priority on each date that a certain district is covered. I have 12 people working on a specific date. I want to make sure district 10,20,30,40,50,60,70, and ...