Dependent list from drop menus

Hey Everyone,

I have an issue with what function to use and how to go about doing it.

my situation is this:

I have one drop down box for the models numbers named "models"
I also have another drop down box for the sizes named "sizes"
and i have a third box for the price.

Dependent on what "Models" and what "sizes" the users choose will determain the price.

is there a way to make this happen. i have over 15 models and 6 sizes with a variety of 6 prices.

I was planning on doing an IF statement 6 times per model but that would mean each cell box is rather full up.
i am thinking that there much be an easier way but i dont know how?

if anyone can help, it would be greatly appreciated.

not really but sort off. In the example you provided, if you select the "category" fruit then the "items" change to fruit.

what i am aiming to do is have a drop down list containing a "catagory" (ie fruit) another drop down list contacting the "sizes" (ie Weight - 1KG, 2KG etc etc) and then the price.

so if i choose from the "catagory" fruit, then from the "Sizes" the price will automatically appear.

eg
Select "apple", select "1kg" = 2.50 and so on and so on

im not sure if i am making any sense!!!!

i was thinking something like: IF Catagory is XYZ and Size is XYZ then price = XYZ

Can you give us some further explanation:

For example, blue values.

You have:
Models TWS-6 to 10 (5 models)
Sizes (6 sizes)
Prices (6 prices)

How are models connected with Sizes?

What if I take TW-9 and 32 GB?
What if I take TW-10 and 32 GB?

Hi SNL888

In the attached, I've defined named ranges for the first 6 items. See if this is doing as you require. If it is, see if you can set up the additional named ranges. Notice the use of Indirect in the Data Validation.

If this is working for you and you need additional help, let me know.

Jaslake,

is there any chance you might be able to explain these formula to me:

=OFFSET(Sheet3!$A$3,0,0,(COUNTA(Sheet3!$A:$A)-2),1)

=OFFSET(INDIRECT($A6),0,0,COUNTA(INDIRECT(A6&"Col")),1)

i got no idea what is going on here or how it works.

If i know this, i might be able to complete the rest of the form.

Thanks for all your help so far.

Hi SNL888

I'll try.

=OFFSET(Sheet3!$A$3,0,0,(COUNTA(Sheet3!$A:$A)-2),1)
Creates a dynamic (as you add items, they're automatically added) named range consisting of all items in Sheet3, Column A, starting in Cell A3. Do some research on Dynamic Named ranges for further explanation. Here's a good place to start http://www.ozgrid.com/Excel/DynamicRanges.htm

=OFFSET(INDIRECT($A6),0,0,COUNTA(INDIRECT(A6&"Col")),1)
This sets the source of Data Validation list in B6.
It says look for a range named as the value of cell A6&"Col" such that if A6 contains TWS_04, the code looks for a named range called TWS_04Col and fills the Data Validation list with that named range. Do some research on the Indirect Function for further explanation. Here's a good primer http://www.cpearson.com/excel/indirect.htm

As you begin to understand how these functions work, you'll understand this

Please Login or Register  to view this content.