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.

0
votes
1answer
12 views

Using CHOOSE with ArrayFormula has different results from Excel

Function CHOOSE within an ArrayFormula seems to yield different results in Google Sheets, compared to Excel. Note how only the first column is shown. Should I report it as a bug, or am I missing ...
1
vote
4answers
36 views

How to add every Sunday in sequential order?

So I tried using the simple formula: =date(2019,3,10) =date(2019,3,10)+7 =date(2019,3,10)+14 =date(2019,3,10)+21 ... etc. But when I highlight the first top 3-4 cells to drag down and formulate ...
0
votes
1answer
26 views

Use of IMPORTRANGE to juxtapose variable-length lists

Let's say I have two Google sheets, Doc1 and Doc2, both having only one sheet featuring two lists with the same schema, say two columns Name and Age. The lists have an unknown number of entries, but ...
0
votes
1answer
21 views

How do I calculate weighted rates in Google Sheets?

I would like to calculate the cost on $D "Rent" with a weighted cost when the respective cell in $B="PB". The calculation would be based on the formula 7x+1.3x=1252 where 7 is dependent on COUNTIF(A2:...
0
votes
1answer
38 views

How to count how often a value is submitted to a Google sheet via Google Forms per day, and put that count in different Google Sheets workbook

I have two Google Sheets workbooks, Workbook A and Workbook B. Workbook A is generated from a Google form that collects email addresses submitted from users. Workbook A has two columns next to each ...
0
votes
2answers
22 views

Insert column into Google sheet generated from a Google form to calculate the difference between two columns of data

I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out." I want to insert a column next to Mileage In and Mileage Out that shows the difference between ...
0
votes
2answers
16 views

Extract and graph information from a days-hours-minutes-seconds format [closed]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
0
votes
1answer
19 views

Drop-down list from cell formula (not data validation)

I am trying to make a triplet dependent drop-list. The first is a typical drop down but the next two are dynamic. Since you cannot enter a formula into the data validation criteria, how can you ...
0
votes
0answers
15 views

URLFetch Being Called Too Often Google Sheets

I'm working on a google sheet for a calculator for a game. I have this script that automatically updates the price of a game item and I grab the API using URLFetch. However, every time I go to sleep ...
0
votes
0answers
21 views

Google Sheets: auto-format an entire column to be hyperlinks upon import

Here's my process: export CSV from CRM > import to Google Sheets > do work. In ColA are unique IDs (all integers e.g. 808938269) of accounts in my CRM. I would like to appended a generic [URL] string ...
1
vote
3answers
151 views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
0
votes
1answer
10 views

Count how many times day was present in in overlapping ranges

Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say: A B 1 27.02.19 15.03.19 2 03.03.19 05.03.19 3 01.04.19 05.04.19 What I ...
0
votes
1answer
41 views

How to insert Mathematical Equations in Google Sheets?

I have been searching for a while now and all searches lead to Google Docs, not Google Sheets. So basically what I'm looking for is similar to the insert equation function as in Excel where you can ...
0
votes
2answers
16 views

How to count duplicate names as one value in Sheets?

I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
0
votes
1answer
34 views

Google Sheets Conditional Formatting Isn't Working

I am making a spreadsheet in Google Sheets and my conditional formatting works in some cells and not in others. I have gone so far as to delete all of the formattings for the entire workbook and ...
0
votes
0answers
20 views

Google Script to Show Rows if cell value

I am working on putting together an Expense Report that allows a person the option to add another expense (up to 23 expenses allowed). I want the rows that need data entry to be hidden unless the ...
0
votes
0answers
18 views

Sumproduct and countif question

I have a workbook that has a separate sheet that is assigned to each classroom teacher. In the column A of each sheet I have the student's names from their rosters listed, in column B I have it set up ...
0
votes
1answer
21 views

Changing the order of a Google Sheets document

I have a Google Sheets document containing names, addresses, birthdates, etc. I need the document to read in order of birth months: January, February, March, etc. How can I do this?
0
votes
1answer
24 views

Count attendance percentage for top 10 specific events

Data I have an attendance sheet for multiple events and for multiple people. The sheet looks like this (mock data): X | A | B | C | D | E | F | G | ... 1 | |...
1
vote
1answer
26 views

Return a value from a column if multiple criteria are met

Can anyone teach me how I can return a value from a column if multiple criteria are met where if: a cell in a column of another sheet contains a word in a cell (referenced) the same row in another ...
1
vote
1answer
13 views

How can I divide row data into multiple Sheets and have sort work corretly?

Say I have a Sheet with people info, containing their names and some other info. I add a filter to this Sheet to be able to sort alphabetically by lastname, or any other column. Now I want to add ...
0
votes
0answers
14 views

Help with a script for google sheets so that both ends of a link can be edited

so I found a great answer to a question that I had about connecting two different spreadsheets with a link that would allow you to edit either one and the other would update, I found a great page on ...
0
votes
1answer
145 views

FILTER has mismatched range size; referencing the second last cell in a column in range

I am using a multicriteria filter to check if my sheet contains a certain value combination. The formula should automatically include any new rows added to the sheet. However, if I do it this way: =...
1
vote
1answer
6 views

Find max value in array and return a value in a different array but in the same row

I have four columns, the two in the middle only being there to keep track of other values, not relevant to this. I'm trying to Search for the highest value in, say, D3:D999 Get the row of that ...
1
vote
1answer
18 views

Map Axis Numeric Values to Strings

I have time series data reviewing the quality of an item. The ratings are on a 1-5 scale ranging from "Very Bad" to "Very Good". How can I define this mapping so that instead of seeing marks for 1, ...
1
vote
1answer
10 views

Conditional Formatting Duplicate Invoices in Google Sheets

My workplace uses Google Sheets to track invoices across all locations. The two most important columns are next to each other and titled Invoice number and Vendor. I wanted to highlight the Invoice ...
0
votes
0answers
17 views

Help with a two way link script for google sheets

so I found a great answer to a question that I had about connecting two different spreadsheets with a link that would allow you to edit either one and the other would update, and I found this great ...
0
votes
1answer
16 views

Max value conditional formatting rule not working in Google Sheets

I want the max value in Column B to show up. I put in this formula but it's not working. What am I doing wrong? Here is the table
0
votes
0answers
21 views

Master Checkbox that turns off other checkboxes based on keyword in a different cell

I'm trying to create a master checkbox (actually a few) that toggle on/off various other columns of checkboxes on the same sheet. I have researched this issue and found only solutions either A) ...
1
vote
1answer
20 views

How to import values from the 2nd sheet based on the colum of the 1st sheet?

I have two sheets - Sheet1 includes: col 1 | col 2 [Name] | [phones ] Sheet2 includes: col 1 | col 2 [Name] | [e-mail] I want to importrange if the name on Sheet1 is equal to name on Sheet2.
0
votes
1answer
20 views

Why is alignment off in cells?

When the cell alignment is set to center it's actually more to the right. I've tried several reformating options including reset. The cells have no formulas, just text.
1
vote
1answer
23 views

Google Sheets returns wrong results

Google Sheets gives me the wrong result. In the first row (M38) I write the number manually; in the second row (M39) I have a SUM for my data. Both results are the same, as you see. However, when I ...
0
votes
0answers
15 views

7 days high and low value of a stock formula

How to calculate the 7 days high and low value of a stock in one cell of Google sheet. Last 7 day high value in one cell and last 7 day low value in another cell
1
vote
1answer
29 views

Generate custom sequence in Google Sheets

I would like to create a column with a custom sequence: A-Z,a-z,0-9. (Essentially the Base64 character set with the last two characters, + and /, left out) So, A1 would be AAA, A2 would be AAB ... ...
0
votes
1answer
28 views

Search for text in multiple columns in multiple worksheets

I have 3 columns (Col1, Col2, Col3). All 3 columns are in their own worksheet (WS1, WS2, WS3). I want to search each cell in Col1 see if that cell matches anything within Col2 in WS2 then output "...
0
votes
1answer
30 views

Inaccurate output when using importrange vs manual input

I have a little problem using Google Sheets. I'm using importrange to get raw data from another sheet that I don't own. This is the sample raw data, then import it my own sheet here. Importing data ...
0
votes
1answer
28 views

How can I compute the permutations multiplication “like” table to a linear list?

I have a table that is structured like this. I would like to transform the table into the permutations of the table if we treated like a multiplication table. Into a list that looks like this. ...
0
votes
1answer
27 views

How to move a row into another sheet?

In Google Sheets, I need to hide rows if column H says, "Completed" My preference would be to cut and copy the row to a new sheet if column F reads "Completed", but sounds more difficult. If you ...
1
vote
1answer
7 views

Insert Today's Date in Google Sheets on Norwegian Keyboard

Ctrl+; should be the US way to insert today's date, but this doesn't work on my Norwegian keyboard. Is there somewhere I can look up what it should be?
0
votes
1answer
11 views

How can I navigate inside a spreadsheet cell immediately after typing text?

This seems like it's a blindingly simple question with an obvious answer yet I cannot figure out how to do it nor find solutions online. If I type some text into a Google Sheets spreadsheet cell and ...
-1
votes
1answer
28 views

Count worked days per employee

I am creating a spreadsheet in Google Sheets for a client who wants the sheet to pull data from their Google Calendar to calculate how many days each employee works. They use Calendar for their ...
0
votes
1answer
19 views

Unable to SUM column calculated by Array Formula

I've used Array Formula to calculate the total price for an order and I have a manual column next to it to note what has been paid. When I come to do a comparison of the two though, Google sheets ...
0
votes
1answer
13 views

All text in a cell before the “ - ” or “ : ”

Is the Query function capable of taking the contents of a cell and returning everything before the first instance of - or :? Here is a screenshot of what I mean: Obviously, this isn't my actual data,...
0
votes
0answers
17 views

Google script full up the database

I have make more questions for this topic, the scrips are working, but i will add more cells to the script. The script what i have. function myFunction() { var spreadsheet = SpreadsheetApp....
1
vote
1answer
35 views

Can conditional formatting reference other sheet?

I needed to check which indexes exist on another sheet. So for the A:A range, I made conditional formatting with the following formula: =COUNTIF('Other Sheet'!A:A;A1) That had a red error outline. ...
0
votes
1answer
35 views

Cell with multiple color formatting results only to first color being applied

If a Google Sheet cell has 2 Conditional formattings, one which changes only font color, one which changes only the background, only the first conditional formatting affects the cell. The reason ...
0
votes
1answer
49 views

How to plot weekly, monthly stock price in Google Sheets?

Help me with functions or formulas for google sheet to display UPTREND DOWNTREND BULLISH BEARISH of Daily weekly monthly quarterly stocks as seen in PIcture attached herewith Please help me build a ...
0
votes
0answers
8 views

Google Sheet - Write Notes based on cell reference's formula result

In Google Sheet, I want to create a custom function to write a note based on cell reference's formula result. But based on the discussion on this link, I just able to write the notes ONLY when the ...
0
votes
3answers
60 views

Duration to seconds calculation

There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try ...
1
vote
1answer
12 views

check if cell is empty OR has a value, AND cell in row isn't empty then assess as true or false

I'm trying to format a column so that if the column A cell is NOT empty, AND the column H cell is either empty OR "no" =AND(NOT(ISBLANK($A$1:$A)), (OR(ISBLANK($H$1:$H)), (LOOKUP("no", $H$1:$H))))