Picking from a conditional drop-down list

Pls see attached spreadsheet.

In cell b15 I'd like to have the ability to pick from a drop-down list given by the 'green' shaded area.

In cell b16 I'd like to pick from a drop-down list; the available contract types conditional on the counterparty chosen in cell b15. And this reads from the relationship given in the 'blue' shaded area.

Thanks for your time and effort!
Pick-from-dropdown--conditional-relation
UtredningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brad GrouxSenior Manager (Wintel Engineering)Commented:
I believe what you are searching for is lookup table, see this link for details - http://blog.contextures.com/archives/2013/09/26/conditional-drop-down-lists-in-excel/
0
Glenn RayExcel VBA DeveloperCommented:
It is easier to set up a named range for your conditional data validation if the second set of lookup values are in columns adjacent to each other as in the example article that Brad referenced.  However, so long as your secondary table is sorted properly (as in your workbook), you can achieve the same result here.

1) Create Data Validation for your Counterpart input in cell B15 by referencing cells B5:B7.
2) Create a named range - say "ContractList" using the following formula:
=OFFSET(Sheet1!$C$4,MATCH(Sheet1!$B$15,Sheet1!$B$5:$B$10,0),0,COUNTIF(Sheet1!$B$5:$B$10,Sheet1!$B$15),1)
Note that if your list size changes or location of the ranges change, you'll need to update this formula accordingly.

3) Create Data Validation for your Contract input in cell B15 by referencing the named range "ContractList"
data validationI've attached a modified version of your file to test.

Regards,
-Glenn
EE-Pick-from-dropdown.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UtredningAuthor Commented:
Thank you so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.