A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

2
votes
2answers
62 views

Autosum entire column

On Google Sheets, I enter my sales for the month in Column F. How can I get column F to Auto Sum so that the total always appears below the bottom entry?
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
63 views

Change an input value in a cell to another value depending on what the input was

What I would like to do is change a number input in the rating column (G3:G9001) to the corresponding number of stars (from 1-10). Howerver I could not figure out how to do this. As a test I tried to ...
1
vote
0answers
53 views

Google Spreadsheet adding new lines in between

I've encountered an issue with Google spreadsheet. When I'm trying to add some text, I'm noticing a new line being added in between. Is anyone else having the similar issue?
1
vote
1answer
44 views

How to count specific items in column?

I just started using Google Spreadsheets. I have a question concerning "counting" a specific word/item in cells. Example: Column A 1. HAT 2. HAT 3. SHOE 4. CANE 5. SHOE 6. HAT 7. - 8. - ...
1
vote
1answer
165 views

Create a function to copy range (values only) to a new tab at specified intervals

Looking to find a function that can copy the values only from a range I am importing from the web at a certain time. Have found a lot of functions that are similar to what I need but can't nail one ...
0
votes
1answer
124 views

Applying formula to lookup value in array of cells in Google Form Responses

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then ...
1
vote
1answer
118 views

Filter rows by one unique column of sorted values in Google spreadsheets

I need an equation that filters by unique ID, using only the latest row (most recent row in time), and data found in that row. I have data that is in the following format: ID | Price | Area | ...
1
vote
1answer
134 views

Delete second row in a cell in Google Spreadsheet

In order to create some list, I've been copy some data from the web. Unfortunately it looks like it has been added second row for each record it need. For example: Each record is in the same ...
1
vote
1answer
554 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 ...
3
votes
1answer
100 views

How to merge multiple spreadsheet content having the list of keys to them?

Input: column of Google keys for spreadsheet that contains tables with some data, eg: date | refNum | Name | Description Require: a sheet that will contain a merged table with data from ...
0
votes
1answer
18 views

What's causing / how to get rid of long web calls in Google Spreadsheets

I have a google spreadsheet that's running into performance problems. It will make really long calls and occasionally freezes up the UI. Here's an example of the type of call that's being made ...
1
vote
0answers
93 views

Representing hierarchical data in Google spreadhsheet

Background Smartsheet.com allows hierarchical representation of data in a single sheet {without having to use adjacency lists } smartsheet hierarchical data example Question Do google ...
3
votes
3answers
2k views

Google Spreadsheet: If value of cell equals X, get value of adjacent cell, then add to totals

I have a budgeting spreadsheet, with separate spending categories, and I want to SUM all spending entries based on the category selected. I'm not technical, but here's an explanation of what I'm ...
1
vote
0answers
19 views

In Google Sheets, how do I find elements common to two ranges? [duplicate]

Range 1 cat dog rat bat snake Range 2 cougar butterfly rat bat lizard aardvark kangaroo What formula or script would I use to get Range 3 rat bat Order is not important.
3
votes
1answer
72 views

Give a name to a cell in Google Sheets

Excel has a little box on the upper left corner that displays the name of the cell (for example "A8", "C20", "BC104", ...) and this name can be changed to a string of choice (like "Force", "mass", ...
1
vote
1answer
27 views

Searching through sheets

I need to search multiple sheets like this for certain words like #work and #hobbies and add up the numbers on the adjacent column. ...
1
vote
1answer
250 views

Decimal conversion of time values

What is or can it be done to add 2 cells, that return a time equation? I deal with multiple time entries and having to enter them in a standard format of 4:45 hours or 4:25 hours. I have a decimal ...
1
vote
2answers
64 views

Average two numbers IF the second number exists

I have 2 columns (Google sheets) of numbers similar to: 10.453 12.178 12.654 0 The third column is: if number higher than 0 exists in both columns, display an average of the two else just ...
3
votes
2answers
270 views

Selecting the Max (or MIN) value in a field from only those rows that match criteria in another sheet

I've been looking through the online help, but haven't been able to find the function or functions I need to accomplish the following. I have 1 Google Spreadsheet with 2 Tabs. The 1st tab is a ...
0
votes
1answer
120 views

Conditional deleting in Google Spreadsheets

I'm using two sheets, one with the main values and reference datas. The second sheet is for price calculation. I use =Filter to get the values from sheet one where Sheet1!E2:E <> 1. How can I ...
5
votes
1answer
185 views

Is there an “increment” shortcut in Google Sheets?

Is there an easy way to auto increment integer value in a Google Sheet like Ctrl+A in vim? If not, how do I go about creating such a shortcut?
1
vote
0answers
9 views

freeze column according to column heading

I am working on attendance in Google spreadsheet, I want to protect the date column which is past date and future date, I just want to give edit right only for today date.
1
vote
0answers
253 views

How can I make Google Spreadsheet recognize dd/mm/yyyy format as a date?

I have a table copied over from other applications to a google spreadsheet. Before processing, I noticed google spreadsheet doesn't recognize 'dd/mm/yyyy' (e.g. 15/01/2015) as a date, instead it treat ...
1
vote
1answer
68 views

Google sheets: conditionally change sign of decimal value in cell for each row

Here's a preview of the spreadsheet I'm editing in Google Drive: The transaction type cell has a value of "debit" if the row is for an expense, and "credit" if it is income. I would like a script ...
1
vote
0answers
167 views

Formulas mix up references when sorting data in Google Spreadsheet

I have a Google Spreadsheet that has formulas embedded into various cells. When I sort the data, the formulas always get messed up, and no longer pull data from the proper reference cells. How do I ...
1
vote
0answers
31 views

Retrieve row data based on indirect Max function value

This spreadsheet tracks live song ratings for a popular band. Users input their score in their own column and those scores are averaged into a community score (Column E). Those song averages are ...
3
votes
1answer
210 views

Where does the calculation take place?

In Google Spreadsheets, I have quite a big amount of relatively complex array formulas in many cells. That takes ages to complete calculating. Does the speed of calculation depend on my PC ...
2
votes
2answers
510 views

Google Spreadsheet: Count column if criteria is met in the respective column and another column

Is there a way to COUNT the cells in column A if the cell in column A equals "X" and the adjacent cell in column B equals "January"?
4
votes
1answer
77 views

Return transposed list of all strings in a row in Google Sheets

In my Google Sheet, the range B2:2 contains mostly empty cells, but some cells contain strings. I would like to print these strings into a transposed list beginning at A12. This list should exclude ...
1
vote
1answer
68 views

Google Sheets: average cells in Row 4 up to last filled cell in Row 2

Is it possible to write a Formula in Google Sheets which calculates the average of the cells in Row 4 but only up to the last filled cell in Row 2?
-1
votes
2answers
340 views

Sum rows of a array function that outputs a matrix [Google Spreadsheet]

I got a arrayfunction that is returning a matrix. I want to edit this to only return a vector, where this vector contains the sum of each row. I tried applying sum(), but that summed every element of ...
3
votes
1answer
189 views

Google Spreadsheet Query for unique and “most recent”

Given a Google spreadsheet like this: I would like to create a query that shows only the most recent row for each unique name, so the results would look like this: What would that query look ...
2
votes
1answer
1k views

How do I use a dropdown menu in a cell to populate other cells with information?

I'm trying to build a spreadsheet that allows a user to select a product in a dropdown menu in the first cell and then based on what is selected it will populate the spreadsheet with prices and ...
2
votes
1answer
56 views

Make cell height in one column not affect other columns within the same row in Google Spreadsheets

I have done a schedule in Google Spreadsheet. I set text wrapping to wrap. The problem is that when I write something in column A and the row in column A gets expanded to fit all text all other ...
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 ...
1
vote
1answer
81 views

Can you restrict Google Sheet to editing just text with restrictions on formatting?

I simply do not want people who edit the sheet to mess up the formatting. I'm sharing a file with well over fifty people, and if someone messes up the document, it'll probably set off a chain. How ...
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: ...
-1
votes
1answer
159 views

google sheet conditional formatting

i'd like to change the date / time that passes the current time to turn red. i'd like to change the date that passes the current time to turn red. the table has dates and time in different formats, ...
4
votes
1answer
274 views

How to add a line break in a custom date format in Google Spreadsheets?

Google Spreadsheets give us a tool to create and use a customized data format for date values. While the tool allows us to use arbitrary characters, like slashes and commas between the date fields ...
1
vote
2answers
420 views

Google Sheets rounds numbers where not wanted

This: 3.1415926535 produces: 3.141592654 How can I have it output the whole number as is? I was using this with TO_TEXT(), getting the same result, but I guess the problem lies prior to ...
2
votes
1answer
35 views

Print text associated with a value

I have a Google Sheet that collects data and analyses any trends. What I'm trying to do is figure out how to output the text value associated with the numerical value. Example: Apples 7 ...
4
votes
2answers
1k views

ImportRange with preserved formatting in Google Sheets

I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells. Cell reference with colour formatting This would be the perfect option, except I ...
4
votes
2answers
305 views

Auto-update of exchange rates in Google Spreadsheets

How can I create a cell in Google Spreadsheet that automatically updates its value when its respective value in the web (i.e. exchange rates) changes? Is there any function that allows to retrieve ...
1
vote
0answers
329 views

Cell-dependent cell ranges in data validation in Google Sheets

Regarding Google Sheets -- is there any way to have cell-ranges in data validation depend on the cell itself. For instance, I want G7 to accept input only from E7 and F7. Similarly, I want G8 to ...
0
votes
0answers
426 views

Google Sheets trendline broken; file unavailable

In Google Sheets, I've created a line graph. I want to add a trend line. I do so by clicking the blue line and selecting Linear from the trendline dropdown. No trendline appears. Then when I right ...
2
votes
1answer
1k views

How to get historical currency data in Google spreadsheets?

I use this formula to get the currency: =GoogleFinance("CURRENCY:EURUSD", "price", DATE(2006,10,2)) But when I enter a date before 2006-10-2 I get an error. Is there any way to get older data ...
5
votes
2answers
371 views

With Google Docs/ImportHTML(), how do I then modify the data?

I'm using ImportHTML() on a Google Sheet to import a large table from a website. It's great, and it's now all there, but now I want to hand-modify the data to my needs. Unfortunately, every time I try ...
2
votes
1answer
113 views

Creating multiple time stamps on google sheets to make dependent drop-down boxes work

I read so many forums but cant find an answer to what is likely a simple matter. I need Time Stamps on 2 different sheets to make 2 different Dependent Drop Boxes work. I use the script below but can ...
2
votes
1answer
53 views

Apply formula when other is modified

I´m trying to create a spreadsheet to manage a business, in particular I want to use it to register the sales of the day. The problem i´m having in particular is that I need it to copy the price of ...