Complicated formula not working in 97

Adam Kroger · 12-04-2005, 01:30 PM

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 · 12-04-2005, 01:55 PM

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

Niek Otten · 12-04-2005, 02:25 PM

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.

--
Kind regards,

Niek Otten

"Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
news:ObadnfYA2sTVug7eRVn-gw@comcast.com...
> 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
>
>
>
>
>

Adam Kroger · 12-04-2005, 03:35 PM

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.

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43932C97.10ACA3E@verizonXSPAM.net...
> =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
>>
>> 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

Adam Kroger · 12-04-2005, 03:35 PM

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().

"Niek Otten" <nicolaus@xs4all.nl> wrote in message
news:ejvDC$P%23FHA.360@TK2MSFTNGP09.phx.gbl...
> 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.
>
> --
> Kind regards,
>
> Niek Otten
>
>
> "Adam Kroger @hotmail.com>" <adam_kroger<nospam> wrote in message
> news:ObadnfYA2sTVug7eRVn-gw@comcast.com...
>> 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 · 12-04-2005, 05:45 PM

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
>
> 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.
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43932C97.10ACA3E@verizonXSPAM.net...
> > =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
> >>
> >> 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

--

Dave Peterson

Adam Kroger · 12-04-2005, 06:55 PM

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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43936196.39C86823@verizonXSPAM.net...
>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
>>
>> 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.
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:43932C97.10ACA3E@verizonXSPAM.net...
>> > =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
>> >>
>> >> 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
>
> --
>
> Dave Peterson

Dave Peterson · 12-04-2005, 07:10 PM

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.
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:43936196.39C86823@verizonXSPAM.net...
> >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
> >>
> >> 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.
> >>
> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> >> news:43932C97.10ACA3E@verizonXSPAM.net...
> >> > =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
> >> >>
> >> >> 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Biff · 12-04-2005, 07:20 PM

>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

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:439374D3.60B73DB3@verizonXSPAM.net...
> 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.
>>
>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> news:43936196.39C86823@verizonXSPAM.net...
>> >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
>> >>
>> >> 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.
>> >>
>> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>> >> news:43932C97.10ACA3E@verizonXSPAM.net...
>> >> > =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
>> >> >>
>> >> >> 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
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson

Adam Kroger · 12-04-2005, 08:55 PM

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?

"Biff" <biffinpitt@comcast.net> wrote in message
news:O9z83iS%23FHA.1288@TK2MSFTNGP09.phx.gbl...
> >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
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:439374D3.60B73DB3@verizonXSPAM.net...
>> 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.
>>>
>>> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>>> news:43936196.39C86823@verizonXSPAM.net...
>>> >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
>>> >>
>>> >> 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.
>>> >>
>>> >> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
>>> >> news:43932C97.10ACA3E@verizonXSPAM.net...
>>> >> > =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
>>> >> >>
>>> >> >> 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
>>> >
>>> > --
>>> >
>>> > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>
>