Complicated formula not working in 97

[Adam Kroger]

Below is the formula (spaced to make the IFs easier to read).

=IF(
AND(AND(ISTEXT(B4),NOT(B4="")),AND(ISTEXT($B$1),NOT($B$1=""))),
==>TRUE
IF($B$1="IS_weap_list",
==>#NAME?
IF(HasFormula(VLOOKUP(B28,IS_weap_table,MATCH("Dam",IS_weap_head,0),0)),
==>TRUE
UseSameAs(VLOOKUP(B28,IS_weap_table,
MATCH("Dam",IS_weap_head,0),0)), ==>NAME?
VLOOKUP(B28,IS_weap_table, MATCH("Dam",IS_weap_head,0),0)),
==>NAME?
IF(HasFormula(VLOOKUP(B28,CL_weap_table,
MATCH("Dam",CL_weap_head,0),0)), ==>#VALUE!
UseSameAs(VLOOKUP(B28,CL_weap_table,
MATCH("Dam",CL_weap_head,0),0)), ==>#VALUE!
VLOOKUP(B28,CL_weap_table, MATCH("Dam",CL_weap_head,0),0))),
==>#N/A
"")
==>""

The functions HasFormula, and UseAs are UDFs from
http://www.mvps.org/dmcritchie/excel/formula.htm They are inserted in their
own module.
The result of the formula is #Value

The following data is in the cells referenced.
B1 = IS_weap_list
B28 = ER Large Laser
The lookup retuns a formula under "Dam"


The formula:
=IF(ISTEXT(B5),
IF($B$1="IS_weap_list",VLOOKUP(B5,IS_weap_table,MATCH("Dam",IS_weap_head,0),0),
IF($B$1="CL_weap_list",VLOOKUP(B5,CL_weap_table, MATCH("Dam",
CL_weap_head,0),0),"")),"")


Returns the proper value of 8

 

[Dave Peterson]

=hasformula() expects a range, but you're returning a value (string/number with
=vlookup):

IF(HasFormula(VLOOKUP(B28,IS_weap_table,MATCH("Dam",IS_weap_head,0),0))

maybe if you're returning a value that looks like an address (Like C99)

IF(HasFormula(indirect(VLOOKUP(B28,IS_weap_table,
MATCH("Dam",IS_weap_head,0),0))), ...

Or maybe that name error occurs because you don't have a range named
IS_weap_table or is_weep_head???

"Adam Kroger

 

[Niek Otten]

Hi Adam,

Why not break up this monster formula in manageable pieces?
Even if you trust yourself to reliably evaluate such a formula, I would
never trust that anyone else would be able to help me if I get into
problems.

 

[Adam Kroger]

Indirect() doesn't change anything, except the error now reads VOLITILE

The ranges are properly named, they are the same used in the second formula
wich works.

 

[Adam Kroger]

The formula is a bit of a monster, hence the subject line. but it is pretty
well broken up. Especially with the second formula as an example working.
The VLOOKUP()s and the AND(ISTEXT()<NOT(=)) are working properly. Therefore
IMO the problem is stemming from the HasFormula() and the UseSameAS().

 

[Dave Peterson]

I don't think I've ever seen the "volitile" error show up.

What part of the formula gives that error message and what's the exact wording?

"Adam Kroger

 

[Adam Kroger]

In the "Edit Formula" window I was referring to the message that appears to
the right as excel is evaluating the formula.

 

[Dave Peterson]

Sorry, I still don't have a guess.

"Adam Kroger

In the "Edit Formula" window I was referring to the message that appears to
the right as excel is evaluating the formula.

 

[Biff]

Indirect() doesn't change anything, except the error now reads VOLITILE

That's not an error value or an error message.

That simply means that the formula is volatile due to the use of Indirect
(and possibly the UDF's) and the evaluated result may not be the same as the
calculated result.

Biff

 

[Adam Kroger]

Unfortunately it still returns #VALUE! wether I use the INDIRECT() or not.
I will also sometimes (not always) get a VB compile error on the UDF. Could
it be the fact that I am using '97 instead of a newer version?

Indirect() doesn't change anything, except the error now reads VOLITILE

That's not an error value or an error message.

That simply means that the formula is volatile due to the use of Indirect
(and possibly the UDF's) and the evaluated result may not be the same as
the calculated result.

Biff