• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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
0
Utredning
Asked:
Utredning
2 Solutions
 
Brad GrouxCommented:
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
 
UtredningAuthor Commented:
Thank you so much!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now