OK...Here's what I did to mock-up your scenario:
• Created a sheet named Lists
• Put these values in A1:B17
• Convert that range to an Excel Table
...Select A1:B17
...Home.Convert_to_table
......Select a style
......Check: My table has headers and Click: OK
...Change the name of the table to: tblRefList
...Created Named Range for the data sections of the Name and PartNum fields (by using Formula.Create_Name)
• Put these values in D1:D4
• Convert that range to an Excel Table
...Select D1:D4
...Home.Convert_to_table
......Select a style
......Check: My table has headers and Click: OK
...Change the name of the table to: tblNames
...Created Named Range for the data sections of the Name field (by using Formula.Create_Name)
• Add a sheet name Input
B3: SelectName
C3: SelectPart
• Create data validation for B4 that refers to the NameList named range
• Select C4
• Create a Dynamic Named Range that will contain only the part nums for the Name in Col_B
...Formula.Name Manager...Click: New
......Name: PartList
......Refers To: =OFFSET(PartNum,MATCH(Input!$B4,Name,0)-1,,COUNTIF(Name,Input!$B4))
......Click: OK
• Create data validation for C4 that refers to the PartList dynamic named range.
Copy B4:C4 down as far as you need.
After selecting a name in Col_B, the dynamic named range associated with the Col_C input cell will reference and list the partnums for the Col_B name.
That way, since a user can only select one DV cell at a time, you only need one (dynamic) named range.
See attached file.
Is that something you can work with?
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks