Question on Formula Audit function
 

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,FAL SE)*E$69/HLOOKUP(EOMONTH(E$71,0),$E$2:$V$8,7,FALSE))

--
Chuck

Question on Formula Audit function
 

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
--

Best Regards,
FARAZ A. QURESHI


"Chuck" wrote:

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,FAL SE)*E$69/HLOOKUP(EOMONTH(E$71,0),$E$2:$V$8,7,FALSE))

--
Chuck

Question on Formula Audit function
 

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
AuditingEvaluate 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".


--
Biff
Microsoft Excel MVP


"Chuck" wrote in message
...
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,FAL SE)*E$69/HLOOKUP(EOMONTH(E$71,0),$E$2:$V$8,7,FALSE))

--
Chuck

Question on Formula Audit function
 

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


"FARAZ QURESHI" wrote:

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
--

Best Regards,
FARAZ A. QURESHI


"Chuck" wrote:

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,FAL SE)*E$69/HLOOKUP(EOMONTH(E$71,0),$E$2:$V$8,7,FALSE))

--
Chuck