Combining two or more drop down menus

[Guest]

I would like to build a worksheet for pricing on boats. Each engine option
has a different price. In addition to that, some boats have different
package options. For example:

A 20' boat has different 3 differerent packages from which to choose:

1. Signature Edition
2. Limited Edition
3. Team Edition

After selecting an "edition" or package you then choose from 3 different
engine options.

1. 275 hp
2. 330 hp
3. 360 hp

Each boat (19', 20' 21', 22', etc.) has a different price based on the
package and then based on the subsequent engine option.

What I would like to do is choose 1.the boat, 2.the edition or package, and
then 3.the engine, at which point the spreadsheet generates a price.

Assume the cell is B6 here: Under the Date/Validation menu, the source needs
to have the "=INDIRECT(B6)" formula while it also must have the
"=IF(D1="","",VLOOKUP(D1,COSTS,2,0))" formula.



My question is how do I combine a dependent list with a
"=IF(D1="","",VLOOKUP(D1,COSTS,2,0))" function so I can generate a price
based on 3 different drop down menus?

 

[Bob Phillips]

=IF(OR(D1="",E1="",F1=""),"",INDEX(P1100,MATCH(1,(M1:M100=D1)*(N1:N100=E1)
*(O1:O100=F1),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)