One of productivity apps in Google Drive, that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

0
votes
0answers
48 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
29 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
71 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 ...
1
vote
2answers
101 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
71 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
48 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
135 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 ...
2
votes
1answer
74 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 ...
1
vote
1answer
273 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
40 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
49 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
30 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 ...
1
vote
0answers
24 views

How can I check emails against a Google sheet containing a list of email addresses?

I am conducting a research study and receive many emails (via Gmail) from potential participants. I do an eligibility screen with participants over the phone, and if they are not eligible, I add their ...
0
votes
3answers
38 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
52 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, ...
2
votes
0answers
86 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
43 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
33 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 ...
1
vote
1answer
276 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
201 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
185 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
34 views

Google Sheets Query Formula Needed

https://docs.google.com/a/fivetowns.net/spreadsheets/d/1myKz4WpRAt3Kja__Dsddnr3YtfOjVPkYUOTXoi-4exc/edit#gid=1665161315 Above is link to mock up of my spreadsheet. I would like to build a formula ...
0
votes
0answers
144 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
176 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 ...
4
votes
2answers
165 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
76 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
46 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 ...
1
vote
1answer
61 views

Apply complicated formula to whole column

I tried searching for this but to no avail - how can I copy a quite complicated formula through the whole column AND make it apply to rows which might be added to the bottom of the sheet? The formula ...
2
votes
1answer
684 views

Conditional formatting in Google Sheets: How do I check if a cell is blank?

In my exercise tracking spreadsheet, I want blank cells in columns B:O highlighted if column A is today's date, and the cell itself is blank. I have tried this: =AND(ISBLANK(B3),$A:A=INT(NOW())) ...
0
votes
0answers
19 views

Google Sheets - Checking duplicates without direct access

I have set up a spreadsheet on Google Drive that multiple users can access and edit, but I also need users to be alerted when the data they enter already exists elsewhere (This could be another sheet, ...
1
vote
1answer
49 views

Copy formula from Google Sheets, paste as formula in TextEdit

How can I copy formulae (like =D3) from a range of cells (several rows and/or columns), and paste those formulae (not the values) somewhere in like TextEdit or anywhere else? Normally it will just ...
2
votes
1answer
102 views

How to put the edited date of a cell into another cell in Google Spreadsheet [duplicate]

In Google Spreadsheets, is there a simpler way to put the edited date of a cell into another cell. I've set a data validation list: ACTIVE COMPLETED ONHOLD TESTING BUGFIXING to F column. If the ...
1
vote
1answer
242 views

How do I Import a Range from one Google Spreadsheet into another?

I am getting the same error as described here except not for the same reason. I am using the new Google Sheets, so I am supposed to be able to paste the entire source URL into the sheet, like so: ...
2
votes
1answer
550 views

Filter by Date Range in Google Spreadsheets

The filtering in Google Spreadsheets seems to only offer text based solutions. Is there any ability to filter cells by date ranges like any of the following: Today Yesterday Last Week Since ...
1
vote
2answers
145 views

How to edit a particular sheet using Google Forms

I created a form that has list of items to be selected. I also created a spreadsheet that has a couple of sheets that correspond to the elements in the form. I wanted users to select a category and ...
1
vote
0answers
70 views

Filter from multiple sheets into graph using Google Spreadsheets

Context I've got a big travel expenses spreadsheet. In it, I have daily entries for different expenses labelled with things like Date Currency Ammount Exchange Rate Amount in £ (my native ...
3
votes
2answers
167 views

How do you subtract two time values in military format in Google Apps Scripts for a spreadsheet?

One column is labeled Checkin Time and the other is labeled Checkout Time and finally I have a column that is labeled Total Time Worked. The cells in the Total Time worked column should subtract the ...
3
votes
1answer
25 views

Entire sheet as a function

Is it possible to use an entire worksheet as a function? I would like to be able to define the inputs to the worksheet and then select a certain cell as the output and copy that multiple times. I ...
1
vote
0answers
31 views

Jagged array from Google Spreadsheet range

The range in my Google Spreadsheet looks like this: | A | B | C | D | E | F | ...
0
votes
1answer
61 views

Formula help for mileage calculation/constant

I need to create a spreadsheet in Google Drive that will let me calculate mileage for my employees. I know the exact mileage to the locations they'd have to drive to. What I need to do is have a ...
0
votes
1answer
240 views

RegExp for data validation

I need a help for regexp writing to validate format: any_positive_integer@any_text,any_positive_integer@any_text, ..... To be exact, "any_positive_integer@any_text" separated by commas and inside, ...
1
vote
1answer
34 views

Can you limit the average function in Google Spreadsheets?

I have a table with column A having a 0 or a 1, and column B having a value that I want to average. I know I can use averageif(A:A, "=1", B:B) to get the average of all of the values that have a 1 in ...
1
vote
2answers
57 views

Operations on hyperlink labels of cells

I am using hyperlinks on some price values on cell in Google Docs. So my cells look like this: =HYPERLINK("http://www.site.com/item","$25.00") I am trying to perform a SUM operation on a bunch of ...
1
vote
1answer
275 views

Only display importrange rows that match specific criteria in Google Sheets

In Google Sheets, I'm using IMPORTRANGE to pull in a large list of data (about 400 rows, 6 columns) and I want to only display rows where column D matches EXAMPLE TEXT. Is this a possible? For ...
1
vote
1answer
23 views

Change certain cells back to default value every Monday morning in Google Sheets

I have a Google spreadsheet that ~30 members can edit (one row for each member). I need to create a column showing "yes" as default in each row until the member manually changes it to "no" if he/she ...
2
votes
1answer
35 views

Find first value in list in cell

I keep running into situations where I have a list of values in a cell in Google Spreadsheets, and I need some value out of the list: by list I mean a string of values (generally dates, but could be ...
0
votes
1answer
23 views

Google spreadsheet into email

I created a spreadsheet to track clients contact information. For convenience, I entered their email addresses as a hyperlink. When I click on the link, a blank 'untitled' page opens. I'm not sure why ...
1
vote
1answer
50 views

Reverse (or repopulate) a Google Docs Form

I created a Google Docs form that collects (mostly) biographical and contact data from users. Now I would like to reverse the process and repopulate the form with each user's input, one page per ...
1
vote
1answer
69 views

How to display Image from URL in Google Sheets? (and apply it to multiple rows)

So I created a list of members in Google Sheets, each member having a profile image, name, location, etc. I exported this list from import.io, a data extraction tool. The only problem is that when I ...
1
vote
1answer
50 views

Advanced formula in Google Sheets

I'm trying to make a worksheet that gives graphs of data in other worksheets. I had the idea that the user could choose which worksheet they want to use. For example, there are 3 worksheets with data ...