**0**

votes

**0**answers

17 views

### Excel - Decision-Tree Budget / Shopping List

I'm planning the purchase of a bunch of A/V equipment. There are lots of different options for where we might end up. Some of the equipment is base equipment, we need it no matter what. For some of ...

**0**

votes

**0**answers

12 views

### Cleaning up data fetched from Google Spreadsheet to Excel

I've been trying to synchronize and build a local database with Excel data. I need to build the database and program to create user accounts, manage the packages, track and log the status of the ...

**1**

vote

**4**answers

4k views

### excel / google spreadsheet: count substring occurrences within a cell

I've been scratching my head trying to figure out a way to count the number of occurrences of a substring within a cell.
For example:
| | A |
| 1 |John,John,Tom,David|
What ...

**0**

votes

**1**answer

10 views

### How to convert a duration into a number of hours in Google Spreadsheets?

Trying to organize a timesheet. I have a cell with a duration of, say, 84:30:00
(84 hours, 30 mins, 0 seconds), which has a number format of "Duration".
How can I calculate the total number of hours ...

**-1**

votes

**1**answer

18 views

### Google Spreadsheet - modify other cell

Is it possible for cell A1 to modify the contents or properties of cell B1?
For example: Change text or background color

**3**

votes

**2**answers

48 views

### COUNT all “P” from a row

I have a presence row in a sheet (Google or MS Excel, does not really matter), having values "A" or "P" for each name.
I need to count how many "P" I have, to make the cell RED if there is any "P" in ...

**0**

votes

**0**answers

30 views

### Coordinates of an external named reference — Google spreadsheet [migrated]

In that doc, I've got a column named range: Sheet1!I:I, named NamedRanged1.
In another doc, I'd like to get the index of that named range, ie: 9 (I is 9th column).
I tried with ADDRESS and ...

**2**

votes

**2**answers

2k 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, ...

**1**

vote

**1**answer

22 views

### Populate Spreadsheet with files within a folder

I need to manage a lot of files within folder related to a project, at the moment these files reside in Dropbox. So, I will like to find a way in which I can have a Spreadsheet populate itself with ...

**0**

votes

**1**answer

40 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

23 views

### How can I compare a student's first name, last name, and day of detention to identify what lunch period they have in google spreadsheet?

I have a google spreadsheet that auto-fills last names in column B, first name in column C, and day (not date) of detention in column F.
In another set of columns I have the students' lunch period ...

**0**

votes

**0**answers

4 views

### Is there a way to have a Slack overlay in Google Sheets? [migrated]

Google Sheets lets you chat with other people actively using the sheet, but it has no history and overall feels like the simplest thing that could work.
Slack, by contrast, seems like everything you ...

**0**

votes

**0**answers

24 views

### How do I calculate the sum of 2 columns using the max from each row based on a condition? [migrated]

I have data in two columns. I would like to sum the two columns (A and B) based on the max value in each row and a condition (c) indicating if the row should be included in the sum. Example:
A B ...

**0**

votes

**0**answers

9 views

### How to sort spreadsheet by date+time column but with a modified hour column? [migrated]

I have a spreadsheet generated by a google webform and it produces a standard spreadsheet with a full timestamp column that is two hours behind and a hour column (hh:mm am/pm) that the user fills in ...

**0**

votes

**0**answers

11 views

### Value to drop-down objects?

I think I might be over-thinking, but since I have no experience with any kind of spreadsheet, I just don't know where else to turn.
What I'm trying to do:
I want a drop-down list and each object ...

**7**

votes

**11**answers

20k views

### Can you convert an address to a zip code in a spreadsheet?

Given a column of street addresses with city and state but no zip in a spreadsheet, I'd like to put a formula in a second column that yields the ZIP code. Do you know a way to do this?
I'm dealing ...

**2**

votes

**2**answers

134 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 ...

**36**

votes

**7**answers

110k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**0**

votes

**0**answers

23 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

**1**answer

105 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.

**3**

votes

**1**answer

2k 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

**0**answers

12 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

38 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

55 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

21 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

28 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

**1**answer

31 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

**1**answer

212 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 ...

**0**

votes

**0**answers

18 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

**2**answers

219 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 ...

**1**

vote

**0**answers

88 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 ...

**1**

vote

**1**answer

43 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

23 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

19 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
...

**0**

votes

**1**answer

21 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

90 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

37 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 ...

**1**

vote

**1**answer

67 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

**2**answers

150 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. ...

**2**

votes

**1**answer

163 views

### HTML tags in Google Spreadsheet [closed]

I am experiencing issues with Google Spreadsheet.
The thing is that me and my colleagues are using one spreadsheet as a table for translations, there is a column for English and then German and ...

**0**

votes

**0**answers

46 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?

**2**

votes

**1**answer

1k views

### Remove line breaks within cells in a range (\n) by Google Apps Script [closed]

In a Google Spreadsheet I would normally do the following:
Go to Find and Replace
Check "regular expressions"
Find \n and replace with null
I have searched and for the life of me I'm unable to ...

**0**

votes

**1**answer

13 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

41 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

75 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 ...

**2**

votes

**2**answers

1k views

### Pad numbers to specific length in Google Spreadsheet

I have a column with integer numbers like this: 1, 2, 3, 4, 5 and I want to create another column with these values padded to length of three like this: 001, 002, 003, 004, 005.
Is it possible to pad ...

**0**

votes

**1**answer

861 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

23 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

85 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

1k 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 ...