Suggestions creating a product configurator

[Cytosol]

Hello,

I am attempting to create a product configurator for systems my company sells. The configurator consists of about 5 computer systems, and contains about 10 different configuration options. It also displays pricing and creates a part number for the configuration.

I have this half done; I am able to select systems, select configuration options that create a part number for each component, add up the price in a column, and then concatenate the individual part numbers into a long product configuration string.

I have 2 worksheets; an interface pane containing the configurator for our sales personnel to use, and a data worksheet containing 3 columns for: Items, Config Numbers, and Prices. I am selecting the data and using data validation to pull into drop-down lists, and VLOOKUP to create the configuration number and pull the pricing. This all works very well.

Now my problem:

Some of the computer systems are not compatible with certain components, or only allow one or two components from several configuration drop-downs. My problem lies in defining which items are displayed in subsequent lists when selecting from first ones. I have figured out how to do this on one level (by using =INDIRECT(XXX) in my Data Verification list source field) but it gets tricky when selecting one option affects the results that are shown on multiple configuration lists.

Does anyone have a suggestion on a better way to do this, which will allow for greater complexity and configurability?

I can provide more details about what I have done if needed.

Thanks for your help!

[JBeaucaire]

It sounds like you have a good handle on this. The Dependent Drop downs appear to be the way to go. To be surest of all, you may need to create "versions" of the options drop downs based on the prior choices, so you may have multiple lists for "hard drives" and "NICs" based on the "CPU" selected. There probably isn't an easy way around that much plumbing, but it has the benefit of being accurate, so...

[Cytosol]

Thanks for the suggestion! The way you suggested was the only way I could figure out how to do this.. It would have been nice if there was an easier way, but I couldn't think of it.

I ran into the issue of having multiple lists depend on the selection of one list. Since the INDIRECT function relies on the selection of the previous list matching up with the name of the resulting groups name, I had to create literally hundreds of names for all configurations of the hardware, then have lists be dependant on the selection on 2 different preceeding lists! Some of my names had to contain the type of system, the voltage input, and the hard drive type, so it was extremely convoluted to say the least!

[JBeaucaire]

Yes...and it has the benefit of being accurate, though. If it's something you want us to review directly, post it up and maybe we can suggest something specific.

Click GO ADVANCED and use the paperclip icon to post up your workbook.