Excel Cell yeilds #NAME?

Question

Put a formula in an excel spreadsheet, gives me the correct date. I e-mailed the spreadsheet to my boss, he gets an #NAME error. Same spreadsheet, same formula but he gets an error. Any ideas on how to fix this?

The formula is =IF(I40>0,WORKDAY('Audit Rpt 1a'!B7,((O37+M37)/I40),Dates!B4:B22),"")

His formula is the same, he has all of the referenced sheets, everything traces properly, everything is formated the same as it is on my computer, and he has the Data Anaylsis TookPak add-in checked.

Thanks for all the great ideas. I've tried all of them and it still produces #NAME.

Answer 1

Are you both using the same year and version of Excel? Usually that error is cause by a formula error or a date formatting error.

The formula you provided got chopped after Dates!b...

If that is the complete formula then it is not correct and has a noticible error.
Cannot check for errors with incomplete formula.

Please give the complete formula.

Answer 2

Most likely what has happened is either the cell that has the formula or another cell that it references has an error in the formula. Either a parenthesis or exclamation point or something like that was probably left out or something might have been mis-spelled.

What Excel is telling you is that it is looking for a named range of a certain name in that formula or a supporting formula and it can't find it. So that is why it is giving you the error.

If say for a sheet name or function if you mis-spell it or don't add the exclamation point Excel thinks you are referring to a named range rather than an Excel worksheet function or worksheet name like you may have intended.

When you click on the cell that shows the error, a little exclamation mark symbol thing should pop up to the left of the cell. Click on the exclamation. If Trace Error appears in the dropdown list, click Trace Error. Excel will use a red line to point to the cell that is causing the error. If Trace Error doesn't appear in the list, then the error is due to the formula in that cell.

To better see what I mean, create a new blank workbook. In cell A1, type:

=sum

Then in cell C3 type in:

=A1

Both cell A1 and cell C3 will show the NAME error. If you select cell C3 then click on that Exclamation and select Trace Error it will create a red line pointing to cell A1 since C3's error is due to the error in cell A1.



As far as the apostrophes, disregard what the one person said regarding that. If a worksheet name has a space in it, it has to be surrounded by apostrophes for the reference to work.

Answer 3

"This error occurs when Excel does not understand a name you have used in a formula. Usually the #NAME? error appears when you have used a name that does not exist, or when you misspell an existing name. Another cause of the #NAME? error is when you have tried to type a range, but did not use a colon (eg. A3F11 instead of A3:F11 would cause the error)."

Are you referencing another spreadsheet workbook that your boss doesn't have? Is there a reference to a range of cells that are not available after being emailed? Are you emailing the file to the boss or the spreadsheet as part of the email message? You should be emailing the file as an attachment.

Good luck!

Answer 4

The #Name error in a cell indicates that the formula in the cell is referencing something in the formula that looks like a Defined Name where such a name does not exist therefore the error is generated. The formula that you give as an example will not generate an error as it will be recognized as TEXT and not a formula. If the quotation marks are absent, the @Name error will be presented first because the formula recognizes CAR as a name but the name is not in the Defined Names. If you create the name CAR as a defined range and fill it with numbers, the CAR in the formula will be recognized as a Defined Name and the #Name error will not appear. However, the result of the formula will be nonsense because the formula wouldn't make sense as it stands.

Answer 5

Suggested actions:

Make sure the name exists. On the Insert menu, point to Name, and then click Define. If the name is not listed, add the name by using the Define command.
Misspelling the name. Correct the spelling.
To insert the correct name in the formula, you can select the name in the formula bar, point to Name on the Insert menu, and then click Paste. In the Paste Name dialog box, click the name you want to use, and then click OK.

Using a label in a formula. On the Tools menu, click Options, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.
Misspelling the name of a function. Correct the spelling. Insert the correct function name into the formula by using the Formula Palette.
If the worksheet function is part of an add-in program, the add-in program must be loaded. For more information about using add-in programs, click .

Entering text in a formula without enclosing the text in double quotation marks. Microsoft Excel tries to interpret your entry as a name even though you intended it to be used as text. Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50:
="The total amount is "&B50

Omitting a colon (:) in a range reference. Make sure all range references in the formula use a colon (:), for example, SUM(A1:C10).

Answer 6

is that cell (on your pc) linked to another file? if so, he needs access to that file as well. If the formula refers to values or cells on other worksheets or workbooks and the name of the other workbook ---you will need to correct this.

here are some other reasons:

This error occurs when Microsoft Office Excel doesn't recognize text in a formula.

Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears. Review the following possible causes and solutions:

Using the EUROCONVERT function without the Euro Currency Tools add-in being loaded

The EUROCONVERT function requires the Euro Currency Tools add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.).
Install and load the Euro Currency Tools Add-in add-in

Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
Select Excel Add-ins in the Manage list box, and then click Go.
In the Add-Ins available list, select the Euro Currency Tools check box, and then click OK.

--------------------------------------------------------------------------------

Using a name that does not exist

Make sure that the name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) exists. On the Formulas tab, in the Defined Names group, click Name Manager, and then see if the name is listed. If the name is not listed, add the name by clicking Define Name.

Misspelling the name

Verify the spelling. Select the name in the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , press F3, click the name that you want to use, and then click OK.
Misspelling the name of a function

Correct the spelling. Insert the correct function name into the formula by clicking Function Wizard in the Function Library group on the Formulas tab.
Entering text in a formula without enclosing the text in double quotation marks

Excel tries to interpret your entry as a name even though you intended it to be used as text.
Enclose text in the formula in double quotation marks. For example, the following formula joins the piece of text "The total amount is " with the value in cell B50:

="The total amount is "&B50

Omitting a colon (:) in a range reference

Make sure that all range references in the formula use a colon (:); for example, SUM(A1:C10).

Referencing another sheet not enclosed in single quotation marks

If the formula refers to values or cells on other worksheets or workbooks and the name of the other workbook or worksheet contains a nonalphabetical character or a space, you must enclose its name within single quotation marks ( ' ).

Opening a workbook with a call to a user-defined function (UDF)

A user-defined function (UDF) is called from the workbook but is not available on the client computer. A developer can implement a UDF in several ways. For more information, see Visual Basic help and the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK).

Answer 7

This error can also result if you have an Analysis Pak installed that your boss does not. Some functions are not automatically resident in Excel, but have to be added as an option.

If you can update the question with the erroneous cell formula, we could be of more help.

Answer 8

Your formula has an apostrophe before before the Audit Report reference. I don't have 2007 but this is new to me using 2003. Try removing the apostrophe or including the [ ] brackets around the Audit Report 1 workbook.

God Bless

Frank Pytel

Answer 9

Do you have the cell named or is it making reference to something on a different worksheet?

Sounds like there is some kind of reference missing, which i know would produce the ref error, but theres something different...maybe check your date/time formatting?

Answer 10

Only thing I can recommend is that your boss go into the formula, itself, and make sure all the terms and symbols are correct and identical to the ones you have in your spreadsheet.