**1**

vote

**0**answers

10 views

### Average of last 7 non-empty non-blank cells in google sheets [migrated]

I have numbers in range D7:D and there are times when some cells are empty I want the average of last 7 numbers so if in the last 7 there's only 3 that are full I want it to go back further and find 7 ...

**0**

votes

**0**answers

14 views

### How to convert to insert mode when editing formulas on Google Spreadsheets?

Google Spreadsheet forces you to be in overtype mode whenever you edit an existing formula.
The only way out I know is to type gibberish. That is, for how every many characters you want to insert, ...

**0**

votes

**0**answers

13 views

### Edit multiple form responses inside a google form [migrated]

I am looking an editing form responses on google forms.
So basically, I was thinking to create a simple form for the fields I need and having a drop down on the top to edit form responses ...

**0**

votes

**0**answers

10 views

### Google Sheet get value from a REST API [migrated]

I need to include in a cell on a Google sheet the value of the ticker.last attribute in a JSON which is being constantly updated from a REST API located at https://www.mercadobitcoin.net/api/ticker/
...

**0**

votes

**0**answers

10 views

### Google Sheets - Is there a way to disable a cell from allowing input if another cell has data in it? [migrated]

relatively new here, feel free to point me elsewhere if this isn't the right forum to be asking this.
I'm basically trying to create a simple form on Google Sheets. I'd like to make it so that if a ...

**0**

votes

**1**answer

33 views

### Google Sheets - How to find penultimate (second-last) non-empty cell in a column?

I have a column that'll have dynamic filled in fields but will be empty for the most part. I want to find the difference of last two values found in a column but nothing I've tried seems to work.

**0**

votes

**1**answer

28 views

### Total Unique items in col B for user ID x

Tried everything i can think of that google suggests, will copy and paste a few here for review, but simply put. I have a string of text as an ID say "id-1" I'm trying to count the number of unique ...

**0**

votes

**0**answers

9 views

### How do I dynamically load all data from an XLS file into Google Sheets? [migrated]

I have an automated service that places a new, huge (~6MB: ~20,000 rows) .XLS file into my Google Drive every day. I want to write a Google Sheets .gs script that will run once a day, and load all the ...

**0**

votes

**0**answers

9 views

### Uploading a Google Doc to Google Drive makes file unreadable on Drive website [migrated]

I have files in my computer (Mac OSX 10.10) that were originally created on Google Drive. For example, testing123.gdoc. The file is in my computer because I had it synced with Google Drive via the ...

**0**

votes

**0**answers

11 views

### Depending Data Validation

I want to create a table with select boxes(dropdowns) where the content of the second select is dependend on the first select
Producer | Product | ...
I would like to avoid heavy if/else clauses ...

**0**

votes

**2**answers

31 views

### How to keep a columns formula applied to a newly added row?

I have a recurring formula down an entire column but every time I add a row the formula isn't applied to those new cells in that column. How can I make the formula permanent so it is consistently ...

**1**

vote

**1**answer

35 views

### How to simplify Excel/Google Sheets formula with redundant method calls?

I have an Excel (Well, technically Google Sheets) workbook containing product inventory warehouse storage information. I figured out how to construct queries so it grabs the needed information from ...

**0**

votes

**1**answer

19 views

### copying bank account information into excel / google sheets, lists all info in 1 column instead of grid formation

My bank account informaiton on the website is in a grid formation (ie: name of transaction, value, total in bank)
When I copy and past this information into google sheets or excel, it lists all this ...

**0**

votes

**1**answer

16 views

### How to highlight cells with formulas in Google Spreadsheet

Is there a way to highlight all the cells containing formulas (in contrast with raw numbers) in Google Spreadsheet?
Thanks.

**0**

votes

**0**answers

14 views

### Is there any robust Solver addon for Google Sheets?

I have tried the existing Solver packages there but all of them have their own problems.
Is there any robust Solver add for Google Sheets?
Any package from LibreOffice and open-source community?

**0**

votes

**1**answer

23 views

### Reference a cell with input from another cell

The final objective is to reference the last populated (higher row number) cell from a column (its value).
I found a function that returns the row number of the last populated (higher row number) ...

**0**

votes

**0**answers

17 views

### Spreadsheet styling every X rows

In Google Spreadsheet, how can I change the background color at every X rows.
For example, Let say I need every fifth row to be in gray background, I can I achieve this?
I already know how to do ...

**0**

votes

**0**answers

63 views

### Shortcut keys in Google Sheet don't work?

Background:
I have version 45.0.2454.85 m of Chrome (the latest when writing this question). I have a Swedish version of Logitech MX 5500 physical wireless Bluetooth keyboard.
Problem:
My problem is ...

**0**

votes

**2**answers

84 views

### (Google Sheets/Excel) SUMPRODUCT but ignore empty cells

I'm building my girlfriend a Google Sheets spreadsheet that will allow her to automatically compute her students' grades based on grades she gives her students on different projects, exams, and ...

**0**

votes

**1**answer

82 views

### Fetch Google Sheets data with Excel Power Query

I am using Excel 2103 and Excel power query.
The fetch data from web works fantastically on most websites, however I cannot get it to work on Google Sheets
I have tried using the share URL as well ...

**1**

vote

**1**answer

29 views

### Creating a table similar to pivot table

I'm not sure how to explain it, but I hope this example makes it clear.
Data:
Yes/No | Place1 | Place2 | Place3
----------------------------------
Yes | Thing1 | Thing2 | Apple
No | Apple | ...

**0**

votes

**0**answers

21 views

### Create formula on the fly with Google Spreadsheet

I want to add a formula to each row in a database;
for example:
=query(a1:c5,"select A,'=5')
I want to see 5 and the cell, not '=5',
or
=query(a1:c5,"select A,B,'=regexpreplace(...)')
...

**0**

votes

**1**answer

16 views

### Summing the above 7 rows in same column based on a value

I have a series of values that repeat like days of the week doing down in the same column.
Monday : 2
Tuesday : 5
Wednesday: 6
Thursday : 1
Friday : 8
Saturday : 12
Sunday : 4
Monday : 3
...

**2**

votes

**2**answers

682 views

### Extracting email from text string in excel/google spreadsheet

I'm looking to extract email addresses from text strings in Google Docs/Excel and email them automatically, where the content of the cells are currently unknown (until updated by user).
For example, ...

**0**

votes

**1**answer

18 views

### Format as currency within a formula

I am trying to sum a column of dollar amounts and label it as "Total: $xx,xxx.xx" in the first row of that column.
I have gotten as far as
="Cost - " & Text(SUM(B2:B), "$0.00")
BUT, it is not ...

**0**

votes

**1**answer

47 views

### Microsoft Excel: Merge two sheets and dedup

I have a pretty simple question and google can't seem to return what I'm looking for.
Essentially I have two sheets in one workbook, both contain the exact same columns. The primary key (and first ...

**2**

votes

**1**answer

30 views

### Excel autonumber range

Most of you know about this little feature of Excel, as well as almost any other spreadsheet software like Google Spreadsheets called Autonumbers. Example.
What I'd like to know if it's possible to ...

**2**

votes

**2**answers

91 views

### How to create a data series for exponential decrease diagram

I want to create a pricing model this way:
If you need this product for 50 people, you should pay 2500 (units) per person.
.
.
.
.
If you need this product for 10,000 people, you should pay 300 ...

**0**

votes

**0**answers

30 views

### Google drive spreadsheet is showing black shadow on while typing letters

I mm using Google drive spreadsheet. It is showing a black shadow while typing letters in excel.
How do I resolve this?

**1**

vote

**1**answer

39 views

### Count number of cells that match certain criteria with formulae

Suppose I have column with dates:
2015-01-10
2015-08-12
2014-01-02
2015-01-28
2016-10-14
I want to count how many dates fall to January. I would supopse that formulae is like this ...

**0**

votes

**1**answer

12 views

### Excel - Signs in different columns

Given I have put by signs in a different column, making my data look something like:
How do I write a function to get the sum of these values (=198) with taking the signs into consideration?

**0**

votes

**0**answers

33 views

### Cell formatting in Google Spreadsheets does not get preserved

My Google Spreadsheet does not preserve some formatting. Every time I set, for example, font size to 12 for the whole sheet, newly copied data uses a smaller font size.
This only started being an ...

**0**

votes

**1**answer

59 views

### Combining the ISBLANK and Date function

I have been searching through forums and can not figure out how to succesfully combine the two functions ISBLANK and a date function.
What I need to do is use this formula in google sheets for ...

**0**

votes

**1**answer

411 views

### how to highlight the cell with the max value

given a range of non adjacent columns (not sure if it matters if they're adjacent) i.e
F1:F100,J1:J100,N1:N100
for each row i'd like to highlight the cell with the maximum value.
i tried ...

**1**

vote

**1**answer

22 views

### How to make a formula to calculate how many of each unique item are in a column in the last 365 days

I have two columns A:A (dates) and B:B (items)
.....
I need to find a formula to calculate how many of each unique item are in column B : B in the last 365 days.
In the example the right value in the ...

**0**

votes

**1**answer

68 views

### Run formula from different sheet

Can you run formulas from different sheets? (not simply showing the formula as a string/static text)
Say I type
=Sheet1!A1
where in that cell I have a formula
=concatenate(ice&cream)
...

**1**

vote

**1**answer

719 views

### Copy every nth column on a specific row from one sheet to another (in Google Sheets)

I want a function to select every 2nd column on a particular row and display it in another sheet.
I have two sheets: Sheet1 and Sheet2
In Sheet1 A2 I have a list of numbers that start from 1 to ...

**0**

votes

**0**answers

64 views

### Histogram of dates in Google Sheets or OpenOffice

I have a list of dates that I would like create a histogram of.
date
5/21/2014
5/20/2014
9/16/2014
5/28/2014
6/8/2014
6/8/2014
5/28/2014
5/20/2014
8/11/2014
7/19/2014
8/11/2014
The chart's x-axis ...

**0**

votes

**2**answers

47 views

### Google Docs / Excel how to transform redundant row in columns

I have a list of redundant rows like this
See my Google sheet
Target FRBBED05517
Job state EXECUTION_ORDERED
Target FRBBED05523
Job state EXECUTION_ERROR
Target ...

**1**

vote

**0**answers

36 views

### Where does the calculation take place? [closed]

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

**1**answer

183 views

### Select all cells in a row

How can I select all cells in a row in Excel / Google Spreadsheet?
I know I can select all cells in a column using
A:A
But how can I select all cells in a row?
This doesn't work:
1:1 // select ...

**0**

votes

**1**answer

77 views

### Excel table: Add only certain values (Oscar predictions ballot)

So for tonight, me and friends made a spreadsheet with oscar nominations and predictions of winners. What I'm trying to achieve is that I just put a '1' or an 'x' or something behind the winner in ...

**0**

votes

**2**answers

191 views

### Is there a tool to put a phone number into E.164 format?

I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example:
(123)456-7890 fred ...

**0**

votes

**1**answer

96 views

### Combine GoogleSheets functions

I'm trying to combine multiple functions into one formula in GoogleSheets spreadsheet.
I've tried learning more from Google's Help center and through YouTube but didn't came to satisfactory result.
...

**0**

votes

**2**answers

84 views

### How to remove rows that match?

I have a csv file with list of people (first name, last name, email) and then later on I will get a list of people in another csv file that unsubscribed ( only one column with email).
Q: How can I ...

**0**

votes

**2**answers

129 views

### Spreadsheet finding and sorting most frequent appearance of names

I have a spreadsheet that looks like the following:
https://docs.google.com/spreadsheets/d/1BN3GNRFCsBeHu9gQaKzIo7bfFRkcDqMJ6VMtidEwPD4/edit?usp=sharing
There is a set list of names in Column A. ...

**3**

votes

**1**answer

1k views

### “Trying to connect” error in Google Docs on Safari

Beginning a few months ago, I started receiving a significant number of "Trying to connect" errors when using Google Docs in Safari.
I can load the page fine, and I can even start using it, but ...

**0**

votes

**1**answer

806 views

### How to increment cell references by 7 rows for each row dragged in Excel

In Excel 2007 and Google Sheets, I'm trying to drag a formula to populate cells in a column, but I need each successive row to have the cell references incremented by seven rows. For example, drag ...

**0**

votes

**1**answer

992 views

### How to combine data set to create unique combinations [duplicate]

First of all I don't have experience with Excel scripting, just basic formulas. I've seen this other question on SU but I'm not sure if that's exactly what I need or how to adapt it to my needs.
...

**1**

vote

**1**answer

3k views

### How do I copy rows based on single cell value in Google Sheets?

I have two sheets in Excel, which is duplicated on Google Sheets. Sheet 1 is a list of names with various bits of information for example
Name Number Outcome
Colin Smith ...