Search code examples
exceldependencies

Excel - Indirect drop down population issue

All , I am having a problem in Excel while generating the indirect drop down. There are two drop downs (Coding , LogicCode) enter image description here

Sheet1 contains the data for first drop down and second drop down values.

enter image description here

INT16 , INT16U are the drop down values for Coding in sheet2 . When I select INT16U the values 'Test16U' , 'TT16U' should be displayed in Logic Code drop down. The dependent values for INT16U is displayed correctly but the drop down values of INT16 is not displayed.

correct values for INT16U enter image description here

Issue for INT16 enter image description here

The data validation part for LogicCode column is enter image description here

Can anyone help for this weird problem? Thanks in Advance

Solution

  • You need to name $A$2:$A$3 on Sheet1 as "INT16" with the workbook scope. But it seems this name can't be used, so you need to change INT16 to smth. else. You can abbreviate it as INT16S, WORD, or SHORT.

    enter image description here

    If you limited to use predefined values only, use this solution.

    Add a new row above Sheet1!A. Define names for your ranges in A row, but still use the initial range (it's $A$2:$B$2 now) as 'Coding' drop-down source: enter image description here

    Name "INT16_" = "=Sheet1!$A$3:$A$4" { Scope = Workbook; }
Name "INT16U" = "=Sheet1!$B$3:$B$4" { Scope = Workbook; }
Sheet2!C2 data validation source =Sheet1!$A$2:$B$2
Sheet2!D2 data validation source =INDIRECT(FILTER(Sheet1!$A$1:$B$1,Sheet1!$A$2:$B$2=C2))

    To set default values e. g. on the workbook open event, add the code to ThisWorkbook module:

    Private Sub Workbook_Open()
  Sheet1.[A3] = "TT"
End Sub