This tag is for spreadsheet questions where the question or answers involve an in-the-cell worksheet function, as opposed to some form of code.

learn more… | top users | synonyms

1
vote
2answers
39 views

How to check if a cell contains any of an array of values?

TL;DR: Using SEARCH(search_for, text_to_search), "search_for" is a variable-length array, pls halp On Google Sheets, I have four cells, E1:E4. The values in each cell are as follows: | E ...
2
votes
1answer
17 views

A spreadsheet formula to assign letter grades based on numeric ranges

I'm trying to figure out how to write an IF statement that will be 'true' if a value is in-between two values. Example: If value is between 60 and 69.9 return "D" If value is between 70 and 79.9 ...
2
votes
0answers
12 views

Is it possible to import cell data AND cell format from one Google sheet to another? [duplicate]

I'm trying to import data "and" formatting from one sheet to another. I'm using =importrange("docID", "sheetName!A1:A30") to get the data, but it doesn't get the format. Suggestions?
2
votes
1answer
114 views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
1
vote
2answers
42 views

Function name to add rows in Google Sheets

Is it possible to add rows with a built-in function (such as MIN() or INDIRECT()) in Google Sheets? I would like to fill rows in the main sheet such as +---+----+--------+ | A | 18 | Atext1 | | A | ...
2
votes
1answer
65 views

Have formula copied to entire column in Google Sheets

I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3. I want to copy that function down a row (but need to do this for 560 ...
3
votes
1answer
44 views

Google Spreadsheet Formula to show heading

I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a ...
3
votes
1answer
36 views

How to create multiple columns from one single column?

I've got information that I gather from emails, from where I just take the lines, pass them through a filter and drop them into a Google Spreadsheet sheet. It ends up looking like this: Element 1: ...
1
vote
0answers
12 views

match and lookup function on google sheets match different japanese kanji

Checkout this google sheets: https://docs.google.com/spreadsheets/d/1dw0v_64uN2lCyB7FxfoBmQ5d1OVIBEemxe5TtPaiUFY/edit?usp=sharing Despite the fact that those two kanji characters are completely ...
1
vote
1answer
19 views

Use the order of sheets in which they appear in a Google spreadsheet

Goal What I would like to accomplish is to have my aggregation-master-schedule-sheet (see context) put schedules in the same order that the sheets are. I.e. the left most sheet is 1st, the 2nd ...
1
vote
2answers
55 views

Formula result depending on the week of the day

I've got a bunch of cells that have formulas to display the day of the week according to the date above it. (I'm using Google Sheets by the way) I want the cell underneath the day of the week that ...
2
votes
1answer
282 views

In a Google Spreadsheet, show all combinations for a selection of columns

I have a sequence of letters, in a Google Spreadsheet: | A B C D E F --|---------------------- 1 | U Y 0 4 X N 2 | H X Y 3 | W The first row contains a ...
1
vote
1answer
30 views

How can I import the whole sheet to another Google spreadsheet?

I have a sheet (sheet1A) in Google spreadsheet A, and another spreadsheet - spreadsheet B Now I want to have another sheet on B - called sheet1B - that's totally the same as sheet1A, with data get ...
1
vote
0answers
34 views

Google Sheets: How to print information of user that is printing

Well, I am creating a sheet on Google Sheets and I will share it with some people. The users online can see its contents, but, to maintain the data update information when someone prints that, I ...
0
votes
1answer
66 views

Daily spend vs projection spend pacing (%)

I have a google doc sheet which currently has all our accounts spends.There are two tabs.The first tab details our actual daily spend for each account for each day of the month.The other tab is a ...
2
votes
2answers
46 views

Sum all expenses of a given type given a list of expense types in a separate sheet

I use Google Sheets to keep track of my expenses. The file currently consists of: Sheet 1: "Expense Types" Sheet 2: "2015 Details". The first column of Sheet 1 contains the list of admissible ...
3
votes
1answer
76 views

Google Sheets notification triggered from function result

I have a google form that populates a spreadsheet with a timestamp and two numbers. For technical reasons I can't have the numbers passed directly into a database but they need to be copy-pasted ...
2
votes
2answers
546 views

How to reference a fixed sheet name in Google Spreadsheet

I am doing reports from a set of data in Google Spreadsheet. As of now, I have several sheets, among them Raw Data and Processed Data. In my Processed Data sheet, I use formulas such as: SUM('Raw ...
1
vote
1answer
34 views

Link in Google Sheets to either create a new document or link to the one that was previously created

I'd like to provide a column in a spreadsheet that allows the user to create a note for the column by clicking on a link. If the note has never been created, it will create the link to a new document ...
5
votes
1answer
71 views

How to refer to a cell in the same column at given row?

I would like to get a cell in a particular row above the current cell. This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for ...
1
vote
1answer
38 views

Using IF Formula to get data from another Cell

I am just trying to use the IF formula: COLUMN A COLUMN B COLUMN C Cats NULL Dogs Monkeys Kittens NULL ...
1
vote
1answer
242 views

How do I find what is in a particular cell on the same row as another on based on a match?

I have the following query in Google Sheets: =IF(ISERROR(MATCH(A2,Sheet1!A:A, 0)), "No Match", "Match on Row " & MATCH(A2,Sheet1!A:A, 0)) I want to return the value in column C of the matched ...
2
votes
1answer
46 views

Formula which will grab specific cells and concatenate them together but only if they are not blank

So I have this formula currently, but it breaks when nothing is in any of the cells. I'm expecting this of course, as I haven't told it to do anything when ISBLANK is TRUE. So my question is, am I ...
2
votes
1answer
32 views

How to consolidate information from different sources in Google Sheets

So I have a Google Sheet with several sheets (tabs) in it, like so: Sheet 1 - Weapons Legend A B C D A = Date 5/3 John Sword 10 B = From 6/3 ...
2
votes
1answer
153 views

Formula in Google Sheets to lookup contents of another cell

I'm trying to create a formula that brings back a certain cell depending on what another cell has in it. But the formula I'm trying isn't working. It's working for other things (what I thought was the ...
1
vote
1answer
34 views

Dynamic Values in a formula?

I have a google sheet where I am using this query to pull data from another sheet : =QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...
1
vote
1answer
547 views

Calculate difference between numbers in 2 columns row-by-row in a spreadsheet

I have 3 columns in a Google Docs spreadsheet: A) Inventory purchased price, B) Inventory sold price, C) Difference between A and B (i.e. profit or loss) Data is going to be entered manually into ...
2
votes
1answer
110 views

Google Spreadsheet limit ArrayFormula

I'm using the ArrayFormula to calculate multiple ranges and it's slow because it's trying to calculate everything in that range, even though it has nothing in it. Is there a way I can limit the ...
0
votes
3answers
144 views

Update formula ranges when adding new rows to a spreadsheet

I have a Google Spreadsheet in which I keep a list of expenses. I add a new expense by inserting a new row above the last expense. On the first row of the spreadsheet, I keep a sum of all expenses: ...
2
votes
1answer
91 views

Calculate total based on drop down criteria

I am creating a budget that has different expenses on one worksheet (Expense_Overview). On another worksheet, I have a list of expenses (Expense_Detail) I am entering that are categorized based on a ...
1
vote
2answers
528 views

How do I get the data in a cell of the last column of a row?

I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the ...
1
vote
1answer
69 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
2
votes
2answers
2k 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 ...
0
votes
1answer
1k views

Click cell in Google Spreadsheet to open data in popup

Is it possible to have a cell that just says "click me" or whatever, which opens a popup so you can see the full data? I want to keep all my data contained in one row per item, but one cell per item ...
5
votes
3answers
1k views

How do you replace a formula with its result?

I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of ...
1
vote
1answer
65 views

Search for multiple variables in multiples rows at the same time in Google Sheets/Drive

I'm using Google Sheets (only option, sorry, know this should be a database). Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to. Sheet 2: List of 50 ...
3
votes
1answer
339 views

Is GOOGLETOURNAMENT defunct?

I do not follow the NCAA Division I Basketball Championship but am interested in the accuracy of current advice re Google Spreadsheets. One of the functions is GOOGLETOURNAMENT which, rumour has it, ...
0
votes
1answer
411 views

Using VLOOKUP with dynamic range array

I have a sheet with a list of email address that is being auto-updated (more email addresses being added on a daily basis). I have a second sheet with a list of email addresses that have been ...
3
votes
2answers
112 views

Google Spreadsheets: Identifying and Working with Any Cells that Match

I am trying to find a function, or a series of functions, that allow me to (1) Identify if any cell in column A matches any other cell in column A, (2) add numbers in Column B that occupy the same ...
2
votes
2answers
3k views

Import Google Spreadsheet range if a column contains a particular value

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'. I'm using the following formula: ...
0
votes
3answers
4k views

Counting unique values based on multiple columns

I am working in Google Spreadsheets and trying to do some counting that takes into consideration cell values across multiple cells in each row. Here's my table: |AUTHOR| |ARTICLE| ...
1
vote
2answers
290 views

How can I make a Google spreadsheet Function reference one column's value and adjust it to a different Value?

I am trying to create a spreadsheet that will calculate varying sizes. I need it to reference a key where a letter is an established size such as H=1984, S=672, etc. so as that that column can be ...
0
votes
1answer
203 views

How do I select a range programatically in Google Spreadsheets?

I have a spreadsheet like this: active date name 1 1-Jun-2013 Paul 1 4-Jun-2013 Sheela 1 8-Jun-2013 Paul 1 2-Jul-2013 Henrik 3-Jul-2013 Henrik ...
0
votes
2answers
825 views

Spreadsheet to calculate weekly increase

I'm trying to create a spreadsheet with my weekly cardio and weight training average times and a total of time spent during an average visit. I would like the final column to display the in/decrease ...
2
votes
1answer
13k views

Using INDIRECT() in sheet reference range

I have a separate sheet with some data where a user's input will reference the row number of a sheet. I'm trying to reference that sheet by inserting the column letter and taking a number input from a ...
-1
votes
1answer
1k views

How to get the Elapsed time of two cells hh:mm in Google Spreadsheet

In columns K and L I have time stamps. Let's say 03:30 is in column K and 04:30 is in column L. I want the total elapsed amount of time in hh:mm in column P. I have tried the average function, ...
3
votes
3answers
4k views

Google Spreadsheets: Excluding Negative Values

Is there a way to get a function to return any result below 0 as 0 or not include negative numbers in sums? I'm using it to track my total overtime through the year at work. So right now I have it so ...
3
votes
4answers
4k views

How to get the intersection of two sets

I have two lists (unique sets) in a spreadsheet and I want to get the intersection of the two. Is there an easy way to do this? Here's an example of what I want to achieve: List 1 List 2 ...
-1
votes
1answer
641 views

importxml is not working properly -Google spreadsheet

find attached link for my Google spreadsheet in which i tried to pull out the data from booking.com. i retrieved Hotel name in column. it successfully retrieved the hotel name. but not work with ...
1
vote
1answer
2k views

Nested IF statement on Google Spreadsheet, second part same as the first

I have a spreadsheet for my budget. Payments are either drawn from my bank or my Amex card and then my Amex card is drawn from my bank as well. So I add up all my monthly total like this: ...