|
Complicated formula not working in 97
Below is the formula (spaced to make the IFs easier to read).
=IF( AND(AND(ISTEXT(B4),NOT(B4="")),AND(ISTEXT($B$1),NO T($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,MA TCH("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 |
Complicated formula not working in 97
=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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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 ... Below is the formula (spaced to make the IFs easier to read). =IF( AND(AND(ISTEXT(B4),NOT(B4="")),AND(ISTEXT($B$1),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... =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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... 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 ... Below is the formula (spaced to make the IFs easier to read). =IF( AND(AND(ISTEXT(B4),NOT(B4="")),AND(ISTEXT($B$1),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... =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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
In the "Edit Formula" window I was referring to the message that appears to
the right as excel is evaluating the formula. "Dave Peterson" wrote in message ... 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" wrote in message ... =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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... 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" wrote in message ... =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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... =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),NO T($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,MA TCH("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 |
Complicated formula not working in 97
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... =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),NO T($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,MA TCH("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 |
| All times are GMT +1. The time now is 07:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com