Dropdown lists

[Bubba49]

Hi, I was wondering if someone can point me in the right direction Ive tried google and maybe im not searching with the right words so ill try here. I am trying to use a dropdown list to bring up a group of names from a different sheet. Sheet 2 has 5 groups in columns with 3 names under each title, and I want to bring the info to sheet 1 when i select the group title on sheet 1 from a dropdown list. Hope this makes sense. Thanks in advance.

 

[Toadstool]

Hi Bubba49,

There's a couple of ways I know to do this with Excel 2016:

1. Use named table columns and make the first LoV a list of the column names and the second INDIRECT on the first selected name.
2. Build the second LoV based upon the first LoV selection and use OFFSET for the Data Validation source.

Here's a chain showing option 2: Dynamic drop-down lists

 

[Saurabhj]

Hi Bubba49,

To create dependent drop down, follow below steps: (See images )

1. Select cell C3 and Goto Data menu -> Data Validation -> Data Validation option.
2. In Data Validation box, select List in Allow box and in Source field select the group names in Sheet2 (A1 to E1)
3. In Sheet2, Select data of first column (A1 to A4) and press Ctrl+Shift+F3.
4. In Create Names from Selection box, press Ok. (Top Row option is already selected.)
5. Do Step 3 and 4, for each column in sheet2, which contains data. (B, C, D, E)
6. Select cell C5 and Goto Data menu -> Data Validation -> Data Validation option.
7. In Data Validation box, select List in Allow box and in Source field type: =INDIRECT(SUBSTITUTE($C$3," ","_"))
8. Select Group Name in cell C3, the corresponding items will be appear in cell C5.