#Name? errors but formula works too

[scott]

I have a sheet which continues to show #Name? errors in a column with
a formula doing a few things.

=IF(LEFT($H2,1)="p",$K2,MROUND(AN2*HLOOKUP(AN2,Lookups!$A$33:$J$34,2),10)-1)


This looks at H and if it finds a color "p" it uses a price in K.
If not look at a cost in AN do a lookup in a table for a margin
markup and use it to arrive at a price with rounding.

This sheet is used for giving price quotes over the phone and so the
user usually has calculation set at manual to speed up autofiltering
etc.

When I ltrace the # Name? error in the auditing toolbar it points me
to column H as the source. If you click the cursor within the formula
and then hit return it calculates that formula and shows the result
fine.
Leaving calculation on does not seem to solve this for the user.
I don't have this behavior on the machines I access remotely to see
the workbook.

Any ideas what I can do to maintain the already calculated cells so
that we aren't dealing with these #Name? errors?

The workbook probably has 30,000 formulas plus conditional formatting
in places so perhaps there are not resources to preserve things as
they are?

Thanks for any help
ScottD

 

[Tyro]

You don't state where the #Name error occurs. What cell is your formula in?
What cell has the #Name error?

Tyro

 

[Dave Peterson]

If you're using xl2003 or below, then you have to have the analysis toolpak
addin loaded to use =mround().

Tools|addins
check analysis toolpak

You may need to have the distribution CD available (depending on how you
installed excel).

 

[scott]

Sorry I forgot. Column S has this formula and Q a similar formula.
Excel 2003 on XP Pro machines.

Thanks,
Scott

 

[scott]

Yes the Analysis ToolPak is on these machines.
I will check the user has not removed it however.
Thank You,
Scott

 

[Dave Peterson]

I didn't see anything else in that formula that could have caused a #name?
error.

You may want to break the formula down into smaller pieces to isolate the error
if the solution wasn't the Analysis Toolpak.

 

[Tyro]

You still have not answered my question. What cell, not column, contains
your formula? What cell, not column, has the #Name error?

Tyro

 

[scott]

That is what has confused me because it works usually.
Thanks Dave

 

[Dave Peterson]

Did you try unchecking the analysis toolpak, then rechecking it?

 

[scott]

Well actually getting on that users machine, I unchecked and rechecked
Analysis ToolPak and then let the sheet re-calculate.
Everything shows correctly now. So I guess if they insist on having
calculation off they may have to allow the sheet to re-calculate
occassionally.
Thanks for having me check that.