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

**1**

vote

**1**answer

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

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

**0**

votes

**3**answers

222 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

**1**answer

610 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

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

**2**

votes

**2**answers

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

197 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

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

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

**2**

votes

**1**answer

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

174 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

760 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

973 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

857 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

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

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

**6**

votes

**3**answers

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

**5**

votes

**4**answers

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

199 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

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

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

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

650 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

**2**answers

737 views

### How do I get Excel to automatically replicate functions across the spreadsheet?

I have a spreadsheet where I'm doing a whole table of calculations. I'm playing with the calculations and seeing what it does to the data. I expect to change the calculations dozens of times (at ...

**3**

votes

**1**answer

8k views

### AutoFill Large Number of Cells in Excel?

I have a formula in Excel that I would like to autofill to 999,999 other cells. How can I do this without dragging the autofill handle down the entire sheet?

**0**

votes

**1**answer

755 views

### how to find cells that have a certain text in open office's calc (excel might work also)

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this:
- get all cells that have the text "electric bill"
- get the cell at the right of that one, ...

**4**

votes

**1**answer

3k views

### Using INDIRECT and AND/IF for conditional formatting

I am having some issues with using INDIRECT to apply conditional formatting.
I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater ...

**5**

votes

**1**answer

237 views

### Excel formula needed

I'm not so good in excel but i "think" my problem can be solved using excel.. I've got a column with numbers and i'd like to find the best combination of using these numbers. In
our case the best ...

**1**

vote

**2**answers

415 views

### Filter and reorder columns in Excel

I've got a service (phplist, a newsletter manager) that exports a user list with several fields. At the end of it, each user has one or more lists to which he has subscribed.
The problem is, that ...

**2**

votes

**1**answer

4k views

### What excel formula can I use to calculate the 2nd Monday of a given month?

What excel formula can I use to calculate the second Monday of a given month?
You can assume I have a cell containing the first day of the month to work with.
In the interests of other users, can ...

**2**

votes

**1**answer

571 views

### Populating cells with data from another spreadsheet after just keying in a few letters

I have 1 workbook with 2 spreadsheets. Spreadsheet 2 column A contains a long list of company names, Columns B - H contain critical information about the company.
Spreadsheet 1 contains all of the ...

**0**

votes

**2**answers

752 views

### How to assign row numbers to cells in Excel

I want to make the columns be from 1 to 195, in order from least to greatest, so that if I make a mistake (say, I want to add an entry between row 28 and 29), I want Excel to order itself from 1 to ...

**3**

votes

**1**answer

1k views

### How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.
In the game your character have 9 "slots" for weapons and armors, 1 for ...

**5**

votes

**3**answers

9k views

### Find missing entries from a list of sequential numbers in a column?

I have an Excel sheet containing the results of 66000 test cases. Or, at least it should...
Now, because I was running the tests asynchronously and kept stopping and starting I had the foresight to ...

**0**

votes

**1**answer

1k views

### How to convert decimal years into a date (format dd-mmm-yyyy or similar) in either Excel or SPSS?

I've found a fair number of formulas online which let me convert birthdate into decimal age in years but I can't find something that converts, for instance, 1.0 into 04-Nov-2011.
Any help would be ...

**-2**

votes

**1**answer

134 views

### How do I create a range from a cell value?

For example:
A B
7926 3 FROM =COUNTIF(AQ$7926:AQ7970,"=0")
7926 3 FROM =COUNTIF(AQ$7926:AQ7971,"=0")
7926 3 FROM =COUNTIF(AQ$7926:AQ7972,"=0")
7926 3 ...

**2**

votes

**3**answers

463 views

### How to get a de-duplicated list of row labels and their summed values in Excel 2010

I have an Excel sheet with multiple rows in this format:
Column A|Column B
label | value
--------|--------
header (merged cells)
labelA |12
labelB |5
labelC |9
header (merged cells)
labelA ...

**1**

vote

**1**answer

2k views

### Conditional Data Validation

I've been taking a certification course on Excel. It's a beginner class but something caught my attention. Specifically, Data validation.
We learned how to do it using the default conditions but I ...

**1**

vote

**4**answers

9k views

### Excel formula to convert per-annum interest rate to compounding daily and weekly rates

If I borrow $100,000 at an annual interest rate of 10%, then I would have been charged $10,000 at the end of one year.
However, I want the interest to be calculated daily and compound. If I simply ...

**0**

votes

**1**answer

6k views

### How do I use an OR clause in a COUNTIFS formula?

This formula works, but it'd be simpler with an OR statement in there somewhere...
...

**1**

vote

**1**answer

597 views

### How would I create a function in Google Docs (or Excel) to evaluate column a and return column b value.

I'm using google docs speadsheets to create a dashboard. I have two columns of data. I want to have a cell evaluate Column One for a specific name(for example, "Agency A") and return the value in ...

**2**

votes

**1**answer

78 views

### In Excel, how do I highlight all rows that do not contain “,2”?

I'm verifying the FormatCurrency function is written properly in ASP code and I put everything in Excel from "find results".
I want to highlight only the ones that do not contain ",2" in them.
...

**0**

votes

**2**answers

89 views

### Excel VLOOKUP Function

I'm using the VLOOKUP function and I have this function =VLOOKUP(B9,Products!A5:K29,11,FALSE)
It works but what do I need to add so the cell will display nothing if B9 is nothing? At the moment ...

**0**

votes

**2**answers

47 views

### copy worksheet to several workbook

I created a worksheet/spreadsheet with functions and references to cells in other worksheets/spreadsheets within same workbook to create a report of other worksheets within the same workbook.
I have ...

**0**

votes

**1**answer

3k views

### OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...

**0**

votes

**1**answer

1k views

### How to extract the date of a cell stored as “dd/mm/yyyy hh:mm” format

I have a cell that holds a date/time value in the following format: dd/mm/yyyy hh:mm.
Now I need to extract the date from it as a string and append a different time to it. And I need the final ...

**2**

votes

**2**answers

419 views

### 2 columns: '1' as drop box for location and '2' auto populates with post code

In an Excel 2010 spreadsheet the user will select the location from the drop box. I need to have the correct post code automatically populate the adjacent cell. for example in cell A1 the user selects ...

**2**

votes

**2**answers

483 views

### Converting “in place” a column of cells from minutes to hours and minutes in Excel

I have several columns of data in an Excel 2010 worksheet that are values in minutes and I want to convert them to hours and minutes (the data source will only generate an Excel workbook using ...

**8**

votes

**3**answers

9k views

### Type math formulas in LaTeX way in Microsoft Word?

I wonder if there is some free solutions for typing math formulas in LaTeX way in Microsoft word document (Office 2007)?

**1**

vote

**1**answer

172 views

### Can Excel's ISERROR be used in a cascading manner?

Here is my original formula
=IF(ISNA(VLOOKUP($A3,INDIRECT("'"&G$2&"'!$B$3:$I$516"),6,FALSE)),"✖",
IF(VLOOKUP($A3,INDIRECT("'"&G$2&"'!$B$3:$I$516"),6,FALSE)=$D3,
...