Question on Formula Audit function

[Chuck]

Hi,
I am a worksheet with some rather "involved" formulas. In trying to debug
them, I have been using the Formula Audit. But now I find that sometimes this
works and sometimes it shows me the value #NAME? The formula has no error,
but the Formula Audit box only shows the Evaluate and Close radio buttoms.
Step In/Step Out are grayed out. The formula appears below. Any ideas on what
is causing this?

=IF(HLOOKUP(EOMONTH(E$71,0),$E$11:$V$61,$A72,FALSE)=0,0,HLOOKUP(EOMONTH(E$71,0),$E$11:$V$61,$A72,FALSE)*E$69/HLOOKUP(EOMONTH(E$71,0),$E$2:$V$8,7,FALSE))

 

[FARAZ QURESHI]

Do ensure that Analysis ToolPak add-in is installed and loaded by checking
Addins list in the Tools Menu.

EOMONTH needs the Analysis ToolPak add-in

 

[T. Valko]

I've also experienced this when a formula contains multiple functions from
the Analysis ToolPak add-in.

I posted about this several years ago but no definitive solution was ever
found.

You can highlight sections of the formula in the formula bar and then press
F9 and you'll get "close" to the same thing as using Formula
Auditing>Evaluate Formula. But, this method is limited in the size of the
evaluation. By the looks of your formula you should be able to use this
method. When you highlight a section then press F9, press ESC and it'll
"restore" the normal formula.

If the expression being evaluated is too large you'll get a message that
says something like "formula too long".

 

[Chuck]

Analysis ToolPak add-in is installed and loaded but thanks for the hint.