Questions about using in-cell functions/formulas in spreadsheet software.

**0**

votes

**2**answers

921 views

### Excel: How to print unique pages with only one sheet

I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only.
I am creating ...

**2**

votes

**1**answer

254 views

### Excel: Weighted SUM of IFs [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have a spreadsheet with a list of weights and another list based on a drop-down selection with the choices "Yes" ...

**3**

votes

**2**answers

1k views

### Excel Conditional Formatting

Highlight all non-blank entries in a column, except for the column header, that do no fall in some range.
Here's my attempt:
...

**3**

votes

**3**answers

13k views

### Microsoft-excel newlines and tab

Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE".
Example:
I've string below when I completed join string.
"Today ...

**0**

votes

**2**answers

4k views

### Cell to Equal Value of Different Cell based on Match in Array

Need to Column C to equal the text in Column G based on the match the value in Column B.
I do have Column C matching the row in which the value is found (based on columns B and F)
Column C's Formula ...

**0**

votes

**1**answer

467 views

### Excel 2010: Giving another cell value to an “x” (check) in a cell

Hopefully I'll be able to explain clearly what I would like:
I have a spreadsheet about a car pool. I have to put an "x" (check) in a cell when there are seats available to transport people, but I ...

**0**

votes

**3**answers

5k views

### Sum only if any one of six cells have a value

I would like a cell (total) to sum 6 other cells only if one of those 6 cells have a value. If none of the six have a value i do not want anything to appear.

**0**

votes

**2**answers

184 views

### Finding duplicates in a column where the cells hold a formula

I have a workbook with 3 sheets. Sheet "PS", column A contains a list of player names that stays static. Each week, each person in column A sends me a name from an available list. They are allowed ...

**3**

votes

**2**answers

3k views

### In Excel, can I provide a “default value” in case my formula returns #NA?

For example,
Say the value of a cell is:
IF(ISNA(VLOOKUP(A3,somesheet!G:J,3,FALSE) * VLOOKUP(A3,someothersheet!A:D,4,FALSE)), 0, VLOOKUP(A3,somesheet!G:J,3,FALSE) * ...

**1**

vote

**1**answer

2k views

### Combine cells with binary values in Excel

Imagine I have a couple of eight cells which all contain a binary value; so it shall be something like Cell A1 has value 0; Cell A2 has value 1; Cell A3 has value 0; etc.
What I would like to have is ...

**1**

vote

**3**answers

4k views

### How do I transpose data in columns to rows?

How do I transpose data in columns to rows?
If the data is:
Joe
some city
some state
Bob
no city
no state
and I use Paste Special » Transpose, I get
Joe some city some state Bob ...

**-1**

votes

**2**answers

808 views

### Microsoft Excel - Generating random time invtervals

I have a list of employees around 30. I need to create a simple table that will assign them a specific time that they are to be on the phone and 24 hours later excel will automatically(randomly) ...

**1**

vote

**2**answers

1k views

### Count negative values within date range (month)

I have values in a table like this
Date Values
01/01 5:35
02/01 -7:08
02/01 3:45
15/01 -4:05
02/02 3:25
13/02 4:25
19/02 6:20
I would like to count by month how many 'Values' ...

**1**

vote

**2**answers

7k views

### Excel get cell value which is NOT Match in a row

I would like to search C2:I2 which is the cell value NOT equal to "=No=", which is "ABC", and the formula I would like to store in A2.
I was able to look for some formula which works with MATCH(), ...

**2**

votes

**1**answer

632 views

### Excel formula - getting mapping details from 2 sheets of the same work book

Please can you help me with the below excel formula?
Sheet1:
Col A = Business use case number (BUC001, BUC002...)
Col B = Business use case definition corresponding to the Business use case number
...

**0**

votes

**2**answers

456 views

### How to round up number in Excel

Let's say we have an array of nubmers
$9,333.00
$5,733.00
$21,333.00
$21,833.00
How can we make them round up to
$9,500.00
$6,000.00
$25,500.00
$22,000.00

**6**

votes

**4**answers

47k views

### How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...

**2**

votes

**2**answers

3k views

### Excel VBA Function for Value AND Format Lookup

I need a custom function to lookup both a cell value and copy the source format. I have a list of values in two columns. The first is of integers and the second column is the corresponding text ...

**0**

votes

**4**answers

3k views

### Smooth a curve by calculating / interpolating source missing values

I have a table of values like this one :
18
18
18
46
46
46
46
46
57
57
57
57
57
57
69
69
69
69
69
69
64
64
64
64
64
64
56
56
56
48
48
48
44
or if you prefer :
18
0
0
46
0
0
0
0
57
0
0
0
0
0
69
0
0
...

**0**

votes

**1**answer

275 views

### Lookup is failing to accurately use the lookup value

I am using two Excel workbooks; one is the backend for the other. In Book 1, there are sheets sheets with a $Timeline, $ClientInfo, and $VendorInfo. Book 2 is meant to be a summary record of different ...

**1**

vote

**1**answer

2k views

### MS Excel 2010 - Unprotect Grouping Controls

I have an Microsoft Excel 2010 spreadsheet that I want to share with my team on a read-only basis; I want to prevent them from modifying it, so that I keep control of changes.
It's quite large, and ...

**1**

vote

**3**answers

4k views

### Microsoft Excel - Count the number of values each name has in a single column

I have a CSV file containing 100,000 rows.
Each row consists of information under a number of columns, such as "Date", "Page Accessed", "Page Type", "IP", "User Surname", "User Forename", "User ...

**1**

vote

**1**answer

3k views

### Prevent Excel 2010 user from pasting formatting into cell

Is there a way to lock a cell so that a user can copy/paste a value into it, but not copy formatting to it? Locking the worksheet doesn't prevent the user from changing the formatting of the cell by ...

**2**

votes

**3**answers

405 views

### Update a summary sheet in Excel

I have a spreadsheet that will eventually contain 52 sheets(one for each week) On each sheet there is a cell with a percentage in it. I would like to copy this cell to a summary sheet so that it can ...

**2**

votes

**2**answers

1k views

### “Fill Weekdays” in excel 2010 fills sunday to friday

I have an excel sheet, in which I filled the date 01/03/2013.
I used the autofill feature to fill in the weekdays only, but excel filled Sunday to Friday, instead of Sunday to Thursday.
Any way to ...

**1**

vote

**1**answer

834 views

### Filtering by currency in Excel

I have this situation in Microsoft Excel:
Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...

**0**

votes

**3**answers

238 views

### Excel: Find rows with first column empty

My company uses a giant excel spreadsheet that shows which IP Addresses are being used by which computers (among other information). IP Addresses that are not in use still have a row, but the ...

**1**

vote

**6**answers

25k views

### How to prevent Excel rounding numbers or adding redundant 0's?

I have a column of numbers that appear like this:
but the actual value of the shown cell is 20130.153334
Other values have a different number of decimal places. I don't want to add redundant 0's, ...

**1**

vote

**1**answer

686 views

### merge cells with formula skip duplicates

Our Goal is to merge the names (B) when the data in A is equal. The formula below looks at the next line (A), if it's the same, it merges data (name) from B. The formula is in C, so the result of the ...

**2**

votes

**3**answers

788 views

### Advanced cell selection in Excel

I am new to this flavor of StackExchange, so if this belongs elsewhere, please move it; I figured this would be the best place, though.
I am making an Excel Worksheet that simply stores basic ...

**0**

votes

**1**answer

120 views

### How can I sum a series of tabs across tabs?

I have an excel 2007 file with 13 tabs (a summary and one for each month), and want to produce a sum up across the same cell for each month in the summary sheet.
I have tried ...

**2**

votes

**2**answers

6k views

### Excel: How to return the last Row Number that a contains a specific string?

In excel, is there an excel formula I can use to populate Column B (in my example below) that will return the last row number that contains a specific string, before the current row?
The data is not ...

**0**

votes

**1**answer

217 views

### Excel Pivot Table Calculated Field including LEN

I'd like to setup a pivot table field that counts the number of characters in a given field. LEN([field name]) only returns 1 for every row.

**5**

votes

**2**answers

2k views

### Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:
A B C D
1 Round | Rita | Sue | Bob
------+------+-----+-----
2 1 | 5 | 3 | 4
3 ...

**7**

votes

**1**answer

4k views

### Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes).
I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...

**1**

vote

**3**answers

3k views

### Lookups targeting merged cells - only returning value for first row

I have a master worksheet which contains data that I wish to link to another 'summary' sheet using a lookup.
However, some of the cells whose data I wish to include in the summary sheet are merged ...

**0**

votes

**2**answers

247 views

### Conditional Formatting Non Contiguous Ranges

I have a large spreadsheet, which came prepopulated with data. What I would like to do is the following. If I select I7:15, and apply this conditional formatting
Cell Value is equal to =G7, colour ...

**1**

vote

**1**answer

957 views

### Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...

**1**

vote

**2**answers

2k views

### Why am I getting wrong row with INDEX function?

This image is of my data. I am running an INDEX method to get the invoice number. It is working on all other rows but this one although they all have the exact same formula. I eventually found ...

**0**

votes

**2**answers

1k views

### Remove duplicate rows in excel when not all the columns are the same

How can I automatically remove duplicate rows in Excel when data->filter doesn't work, because the columns aren't all the same?
I believe the picture explains what I need, but I can probably supply a ...

**1**

vote

**2**answers

5k views

### VLOOKUP when Lookup Value is a cell populated via another formula

I need to perform a VLOOKUP, but the Lookup Value is a cell that is populated based on an other formula. The lookup is bringing me back a value of "#N/A". When I recreate the same values in another ...

**1**

vote

**1**answer

1k views

### Obtain value from another column based on selected list value

Say I have two columns, the currency and the exchange rate and on another sheet in the same workbook, I'm referencing the currency as a drop down list. I want to be able to pull the value of the ...

**8**

votes

**4**answers

10k views

### What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...

**8**

votes

**4**answers

37k views

### How to search for newline or linebreak characters in Excel?

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially:
If I hit F2 (to edit) then Enter (to save changes) on each of the ...

**-2**

votes

**2**answers

206 views

### Simple Line Graph in Excel

How can I make a graph with this data:
Club distance club speed
9 iron 85.8 71.7
9 iron 89.8 68.6
9 iron 85.2 69
5 iron 132.5 73.6
5 iron 131.3 ...

**0**

votes

**2**answers

3k views

### Pass values instead of range to VBA Function

I am creating VBA functions in Excel 2007. One has a prototype:
Function ToLevelCode_Range(val As Double, ByRef R As Range)
I can call it this way just fine and it gets the correct result:
...

**1**

vote

**3**answers

3k views

### How can I count unique comma-separated values in Excel 2010

I need to build a formula to walk a column of comma-separated values and count the number of unique values seen. I am using Excel 2010. I have a screenshot of some example data...
The output of ...

**2**

votes

**1**answer

4k views

### Excel 2010 formula to strip out part of cells

I have used basic IF ISNUMBER functions before to identify parts of cells and return the value in a different cell, such as:
...

**1**

vote

**2**answers

782 views

### Excel: include formatting in formula of another cell

I have a spreadsheet which contains a hyperlink based on the contents of another cell. I need the hyperlink to be based on the display (formatted) value rather than the data value of the source cell. ...

**0**

votes

**1**answer

1k views

### Excel 2007: Array formula lookup using INDEX and MATCH is only looking at first row

I have a worksheet, "2011-11", with a series of numbers in column C. The numbers are each in a given currency, identified by a code in column B. What I want to do is write a single formula (in cell ...