**-1**

votes

**1**answer

867 views

### How to make this formula to work in Excel 2007?

How would one make this formula =MAX(LEN(A2:A9)) work in Excel 2007?
I want the value of the longest field in A2:A9 range.
I found this link: http://www.mrexcel.com/td0021.html, but it's old now.

**0**

votes

**1**answer

13 views

### How can I import a TAB delimited file with double quote text qualifier into Excel if a cell value already contains a double quote?

I am trying to import a TAB delimited file with the following format into Excel 2013
"Column A" "Column B"
"tjc0056421151r" "I\gs]hü"nsf^f\F*lqog\düûWp"
"tjc0058088269r" ...

**10**

votes

**2**answers

8k views

### Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?

**0**

votes

**1**answer

18 views

### Excel 2007. I can't enable macro when I open document for second time

The document work perfectly on my computer with office 365, windows 7. But when I copy it to another computer with office 2007 and windows 7, it start normally ( I enable macro and it make everything ...

**2**

votes

**2**answers

21k views

### Finding X on Excel scatter plot/trend line

If I have some data in an scatter plot in Excel, e.g.
X Y
1 10
2 20
3 30
4 40
5 50
and I want to find the Y value for X = 10, or X=3.5, or whatever (obviously this is a simplified example) I've ...

**3**

votes

**2**answers

4k views

### Excel chart won't update, based on calculated cells

I have an Excel document (2007) with a chart (Clustered Column) that gets its Data Series from cells containing calculated values
The calculated values never change directly, but only as a result of ...

**0**

votes

**0**answers

20 views

### How to update a third column from a column when two other columns match? [on hold]

Maybe the heading confuses.
But this is what I want to achieve:
Let's say I have a column, Col1. I have got Col2 that has got some of the items from Col1 and a corresponding Col3. I want to lookup ...

**0**

votes

**1**answer

11k views

### Excel Multiple Cells Included in an IF Formula

I am trying to compare 6 cells, and get a pass or fail at the end, but in Excel it looks like it will only let me have 5 cells. I'm not sure how to word it.
like for example:
Pseudocode: if (A1 and ...

**0**

votes

**2**answers

13 views

### Month by Month Pivot Table

I have a list of dates and data that go back a year. In Excel, I made a pivot table that shows all these dates. In the columns label under data filters I see a lot of weekly, monthly, yearly ...

**0**

votes

**2**answers

26 views

### Excel - Dynamic Cell Update using multiple criterias

I would like to know if there is a way in Excel to create a cell that will display a value using a formula that will calculate the latest entry by date and campaign.
If I have 3 columns DATE | ...

**0**

votes

**4**answers

2k views

### custom format for cells with nothing in them without using conditional formatting

if a cell is blank I would like to add text using a custom format. I've seen where text is added if a value is above or below a certain value. Can you add text to the cell if the cell is blank? I've ...

**0**

votes

**1**answer

34 views

### Is there a limit on the number of rows a sum function can calculate in Excel 2007?

I am trying to sum a column of 50938 values in Excel 2007 but keep receiving a "#VALUE!" error. The cells in the column do contain formulas. I also tried to sum the column after copying and pasting ...

**1**

vote

**1**answer

9k views

### Linking dropdown lists

I would want to link a drop-down list to the values of an adjacent cell. Let say, we have two cells A2 and B2. A2 is a drop-down list with values: Agric, school, sme and housing, while B2, which is ...

**1**

vote

**3**answers

5k views

### How to convert dd.mm.yyyy date format to yyyy-mm-dd in Excel 2007?

My customer entered date in dd.mm.yyyy format. As I want insert all the data in mysql database, I want the date in yyyy-mm-dd format.
I tried to format using Excel custom date format and built-in ...

**0**

votes

**1**answer

43 views

### Sheet name dynamically assigned from a cell value

Sub CreateWorkSheetByRange()
Dim WorkRng As Range
Dim Ws As Worksheet
Dim arr As Variant
Dim template As Worksheet
Dim xTitleId As String
On Error Resume Next
xTitleId = "Select Range"
Set WorkRng = ...

**2**

votes

**6**answers

21k views

### Why does Excel now give me already existing name range warning on Copy Sheet?

I've been working on a Microsoft Excel 2007 spreadsheet for several days. I'm working from a master template like sheet and copying it to a new sheet repeatedly. Up until today, this was happening ...

**0**

votes

**1**answer

20 views

### Microsoft Excel 2007

My gradebook spreadsheet has student assignment headings in columns B-H and their final grade average in column I. Student names are in Column A in descending order.
How can I print out Column ...

**2**

votes

**2**answers

11k views

### Excel workbook protection: how can I stop users tampering with a workbook?

I have a workbook with a lot of sheets, macro's and calculations going on. I now need to send this sheet out (not forseen before yesterday). How do I lock it down so that users:
are unable to ...

**0**

votes

**1**answer

17 views

### SUMPRODUCT where the product is computed from the row

I have a range AG6:AS38. It looks like this:
AG AH AI AJ AK AL AM AN AO AP AQ AR AS
--------------------------------------------------------
6 | 1 A B C D ...
7 ...

**0**

votes

**1**answer

2k views

### Refreshing Pivot Table clears Values and Column Labels fields

When I attempt to refresh an Excel Pivot table (by right click .. Refresh), all of the Column Labels & Values fields disappear, leaving only Row Label fields and an empty Pivot Table. How can I ...

**0**

votes

**1**answer

13k views

### Select Multiple Items from Excel Data Validation List

How can I set up the ability to chose more than one option for a data validation list and list them in one cell? So I have a validation list of A B C D. I want to chose A C D and have those three ...

**13**

votes

**2**answers

75k views

### Run an SQL Query With a Parameter from Excel 2007

I have a query in an excel table. I followed this to do it.
But how can I add a parameter from a cell into my query?
I am looking for a way to do this
select *
from dbo.Customers
where CustomerID ...

**6**

votes

**11**answers

49k views

### Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes ...

**0**

votes

**0**answers

11 views

### Excel 2007 Automatic workbook calculation keeps going to manual

The excel 2007 that I have somehow not updating the formula cells, I go to Excel Option > Formulas > Workbook Calculation and the options keeps going to manual for some reason. I uninstalled Office ...

**0**

votes

**0**answers

13 views

### Outlines of merged cells not staying consistent when a filter is applied

I have a spreadsheet that uses merged cells for visual clarity. The merged cells are in the B column (dates), and in the A column there are various values. I use filters in column A, and in my other ...

**0**

votes

**2**answers

65 views

### How to make data shown on excel drop down list appear as numbers (shortcuts)?

Is it possible to have the data (choices) shown when clicking the drop down list to appear as numbers (or any text other than the actual text from validation data source)?
Let's say the choices ...

**0**

votes

**0**answers

10 views

### Macro Hyperlink Error in Mac OS (Office for Mac 2011)

I am getting an error on a macro only when using a Mac. This macro was designed to solve a problem with a formula using the Hyperlink function to copy a link from another sheet for each row. (Excel ...

**1**

vote

**3**answers

15k views

### Is there a way to fit two pages on one page in MS Excel when printing?

I have a table in Excel. It is 2 pages long, but only half-page wide. Is there a way to print both parts of this table on one page, so one next to another? I don't want to move the data, I would like ...

**0**

votes

**1**answer

22 views

### Excel Month() function does not detect some date format

I have a simple table like this in excel:
The formula i used was =IFERROR(IF(LEN(B3)>0,MONTH(B3),""),""). I check if a cell is blank by using LEN, if so ignore, else, convert it to month no.
I ...

**1**

vote

**1**answer

22 views

### How to use a variable once, across the equation, in Excel 2007?

okay, is there a way to declare a variable inside a cell, without calling the cell name 3 times, across the equation(?)?
I came up with this, to calculate the age from a DOB entered using the ...

**0**

votes

**2**answers

4k views

### How to automatically update values referenced from one sheet to another in excel

Here is my problem. I have 2 to 3 worksheets, and I need to make a master worksheet. The 2-3 worksheets contain the sales made in the day and the customer name. The problem is that new customers come ...

**1**

vote

**2**answers

32 views

### Average the numerical values where an identifier column meets a condition in Excel

In Excel 2007, I have a table with numbers in Columns F, G, H, I, and J. There is an identifier in Column B.
I need the average of all numbers in Columns F, G, H, I, and J where the identifier in ...

**0**

votes

**1**answer

13 views

### How to delete creating automatic back up file for MS Excel?

Every time I use a file Excel it generate an automatic back up file.
How can I disable this feature?
I use MS Office 2007 with Windows 7

**-1**

votes

**0**answers

15 views

### Add a cells content to another cell if cells at the top of the columns have only specific content

I already have a big part of this done thanks to another user, however I need it to go one step further please.
I want to add the contents of cells in a specific row, based on a SUM formula. I want ...

**0**

votes

**1**answer

6k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**1**

vote

**1**answer

23 views

### Lining up sets of data in Excel (both with unique keys)

A related question asks how to line up two sets of data in Excel. I am struggling to figure out how to do this when you have values in both lists which don't exist in the other list. Ie: how would ...

**3**

votes

**1**answer

38 views

### Excel to count number of concurrent sessions based on start/end times

I have a massive set of data that I'm trying to work through. In Column A, I have a username, in Column B I have a session start date/time, in Column C I have the session end date/time.
I am trying ...

**0**

votes

**1**answer

3k views

### How to Find and Replace worksheet name in a formula?

I have 100 worksheets with different names saved in a folder. I have another worksheet in another folder where I conduct analysis of data from one of those 100 worksheets. In this analysis worksheet, ...

**5**

votes

**2**answers

5k views

### Importing multiline cells from csv file into excel

I have a csv file (comma delimited and quoted).
When csv file is opened directly from explorer excel correctly interprets the cells that are mutliline, but it messes up the character encoding (utf-8).
...

**-1**

votes

**0**answers

17 views

### Picking up data in excel with the date check

Let's say I have the following data:
On the first row, there (mostly) will be dates in specific format. But sometimes there can be some texts as well.
Now, I would like to get the average values ...

**-2**

votes

**1**answer

23 views

### Add a cell content to another cell if a different cell has a specific content

How can I fill a cells value (e.g row 29 col A) with =sum(B1:Z1) but then only add the content of a cell in that row 29 if row 4 of that column says "UK IR". That way I can add all the cells together ...

**0**

votes

**1**answer

3k views

### Show or hide rows in an excel sheet subject to the value of a particular cell (excel 2007 & Windows 7)

I have made a spreadsheet to specify decoration selections from a range of standard options.
There are five separate ranges so this is the first selection then within each range there are additional ...

**0**

votes

**1**answer

8 views

### Hyperlinking or linking in another way to a cell without it being able to change

How do I hyperlink or link to a cell from another cell in a way that when rows are added, and the row numbers keeps changing due to new rows of information being added throughout the sheet, the link ...

**0**

votes

**0**answers

10 views

### Auto copy formula in Excel 2007 to next sheet

I run an extremly Large Data qry every morning and dump it into a multi sheet excel file, in that file i have a Sheet set up for just formulas to copy from. Currently i manually copy these formulas ...

**4**

votes

**1**answer

156 views

### How to automatically fill range by interpolating between lower and upper bounds

I apologize I do not have any code or examples too show for this. I am not sure how to even approach or identify the function or result I would like to achieve.
What I have is spreadsheet with ...

**0**

votes

**3**answers

172 views

### How to find how many shops account for 5% of sales

Suppose I have a table where one column is "Shop Address" and another column is "Sales". I want to have a table that shows:
5% of total sales are generated by the top X1 shops
10% of total sales are ...

**23**

votes

**9**answers

95k views

### Find Items in one column that are not in another column

I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A.
What's the quickest way to do this?

**2**

votes

**1**answer

18 views

### Validate that a combination of cells are unique in a row in Excel

I have an Excel sheet where 3 columns (A, B, and C) form a unique identifier for each row, but the values in the columns are not unique. For example:
ABC REF 001
ABC REF 002
ABC ...

**2**

votes

**3**answers

12k views

### How can I link cells between two sheets so that value gets updated on the second sheet

I have a workbook with several sheets. I have dates on the sheets that i update to same value daily.
eg. on sheet 1, the date is in cell A1, on sheet 2, the date is in cell C2, does not follow a ...

**0**

votes

**1**answer

4k views

### How do I get Excel to pull data from multiple workbooks from exact dates based on an entered date

I am running windows-7 and using Excel 2007 Enterprise.
I am a truck driver and need to catalog my pay based on a changing day to day system. I am paid per mile, per stop, per hour, or per flat rate ...