Populate Drop-down list based on selection from previous drop-down list

OK, I found a file close to what I am looking for in This Thread

but I am trying to add costs to the final selection - see attached 20121211 DependentLists reference costs.xlsx

Not sure how to read that, maybe an IF reference, or VLookup?...

I need blanks for the groupings, but the drop downs aren't ignoring them.

I can't get the subtrade drop down selection to be dependent upon distance either.

I have attached a file, I hope you can show me the light!

"I need blanks for the subgrouping" doesn't mean anything to me. I looked at your sheet and the DV in column C is accurately depicting only the values needed from the category selected. It's working. Why break it?

To avoid the blanks, you will need to relist the items in separate area of the sheet with no blanks between....

If the products are all uniquely named, then you can use this formula to get your costs:

=SUMIF('Raw Data'!$B$4:$F$7,C2,'Raw Data'!$C$4:$G$7)

copied down.

This formula in D2 will get the "cost" of the item selected in C2 from the category selected in B2:

=VLOOKUP(C2, OFFSET(Suppliers, 1, MATCH(B2, Suppliers, 0) - 1, 1000, 2), 2, 0)

Cut those cells A13:E24 and paste in J1. Then your original DV formula in column C of your template goes back to working.

Apologies for the delay.. I though JB had this....

See attached.

Note that a major difference between the initial thread you sited and your example is that the sited thread had no merged cells. Merged cells usually cause more harm than good.

Because of these merged cells, we need to create separate lists at least for Distance and Subtrades....

So in Raw Data sheet, column L I listed the Distances, and in M I listed the Subtrades.

I created Named Ranges for each.

Distance: ='Raw Data'!$L$1:$M$1 ( I assume once you have the distances set they will be fixed and not dynamic)

For each Distance, assign the whole column to the name of that distance

Far: ='Raw Data'!$M:$M

Local: ='Raw Data'!$L:$L

Then we name the whole table of Suppliers as:

Suppliers: ='Raw Data'!$B:$G

Now back in the Template sheet, the Data Validations:

For column A, use List: =Distance

For column B, use List: =OFFSET(INDIRECT($A2),1,,COUNTA(INDIRECT($A2))-1,1)

For column C, use List: =OFFSET(INDEX(Suppliers,0,MATCH(B2,INDEX(Suppliers,3,0),0)),3,0,COUNTA(INDEX(Suppliers,0,MATCH(B2,INDEX(Suppliers,3,0),0))),1)

Then in column D, for Costs use formula in D2:

=IF(C2="","",SUMIF('Raw Data'!$B$4:$F$7,C2,'Raw Data'!$C$4:$G$7))

copied down

Hope it helps

Bump - No acknowledgement.

Thanks a lot NBVC, Thats great!

I see the names in column L, M are the key to maintaining the labels in the drop down.

Thanks a lot for your help, I'll be playing around with this and let you know how I get on!