Microsoft Office Excel is a spreadsheet application written and distributed by Microsoft. Use this tag for problems specific to Excel 2007 for Windows.

learn more… | top users | synonyms (1)

-1
votes
1answer
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
1answer
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
2answers
8k views

Keyboard shortcut to switch between sheets in Excel

Does Excel 2007 have a keyboard shortcut that switchs between worksheets?
0
votes
1answer
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
2answers
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
2answers
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
0answers
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
1answer
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
2answers
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
2answers
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
4answers
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
1answer
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
1answer
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
3answers
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
1answer
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
6answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
11answers
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
0answers
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
0answers
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
2answers
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
0answers
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
3answers
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
1answer
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
1answer
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
2answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
3answers
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
9answers
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
1answer
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
3answers
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
1answer
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 ...