Dependent Form Control Combo Box Formulas HELP!

Hey guys,

I have been developing an order form for my department to provide to a client and have run into an issue. The form has simple categories with form control combo boxes that output a value to a linked cell. i then use the =INDEX formula for an adjacent cell to display the price for the correlating value so that it "looks" like they are selecting a product and the price is displayed. all good and well.

The problem is my boss wants me to now set a precedent for the drop down boxes. The user first selects the carrier which will determine which selections are available in the drop down boxes.

I am beginning to think that this cannot be done with form control drop downs. I really dont want to get into VB code because i simply dont know it.

Is there some kind of work around for this?

Thank you!

@ChemistB

yes that could work. Please see what I have done so far. Currently I have an AT&T section and a Verizon section both with the same categories for selection but the selections are dependent upon which carrier you want to go with.

Ideally I would have just one section and the drop downs are dependent based on the carrier you initially select. The kicker is the prices would have to reflect the appropriate selection.

Please check out sheet 2 for the data tables.

Thanks for your quick reply. I figured i would have to initially use the data validation style selection drop down box before i could proceed to my current set up.

test.xlsx

also note the =index formula is only set up for the first drop down. i intend on using the same formula for appropriate prices

OK, here is my finished sheet. I would love if we could brainstorm a way to make this more simple. any input is appreciated. Thank you!