need help populate by drop down list

Copper Contributor

I need help to populate another sheet using a drop down list and getting data from another sheet.

for example:

-on sheet 1 column A is called "group 1", column B is "group 2" and column C is "group 3".

- on sheet 2 there is a drop down list with group 1, group 2 and group 3 and on cell A1 "Name" is written.

- what I want to happen is if, for example, I choose group 2 all the names under Group 2 or column B in sheet1 will show under "Name" in sheet2.


All the tutorials I looked at is based on rows not columns. I am also a beginner in excel so I would appreciate any help.

6 Replies


To create a dropdown list on Sheet 2 and display the names from Sheet 1 based on the selected group, you can follow these steps:

  1. On Sheet 2, enter the text "Name" in cell A1. In cell A2 (or any other cell where you want to place the dropdown list), create the dropdown list.
  2. Select cell A2 (or the desired cell) and then click on "Data" in the menu bar.
  3. Choose "Data Validation" from the dropdown menu. The "Data Validation" dialog box will appear.
  4. In the "Data Validation" dialog box, under the "Settings" tab, select the option "List" from the "Allow" dropdown menu.
  5. In the "Source" field, enter the range that contains the group names. For example, if your group names are listed in columns A, B, and C on Sheet 1, you can specify the range as "Sheet1!A1:C1".
  6. Click "OK" to create the dropdown list. Now you can select a group.
  7. To display the corresponding names based on the selected group, enter the following formula in cell B2 (or the desired cell):

=INDEX(Sheet1!$A:$C, MATCH(A2, Sheet1!$A:$A, 0), MATCH(B1, Sheet1!$A$1:$C$1, 0))

Here, it is assumed that the selected group is in cell A2 and the heading "Group 2" is in cell B1. You can adjust the cells accordingly.

  1. Drag the formula down to display the names for the other groups.

The formula uses the INDEX and MATCH functions to find and display the name based on the selected group and the corresponding column in Sheet 1.

This way, the names from Sheet 1 will be displayed on Sheet 2 depending on the selected group in the dropdown list. The text and steps were created with the help of AI

Hope this will help you.

The formula should be below "Group 2" in Sheet1?

here's the exact placement:
-Group 2 is on H1
-the names are on H4:H617

-Names is on H7
-the names should show from H8:H620 right?


Here are two older examples that I found in my archive.

One is with formulas the other also includes VBA code.

Check it out, maybe it's in the right direction. If not, please just report back.



to understand better here is the file I have been working on.

Please describe step by step (cell by cell) exactly what you want to do in this file.
data in 1st sheet shows in 2nd sheet.
example I choose "ADH" in the drop down menu in 2nd sheet all data in "Column" which has "ADH" in 1st sheet shows in 2nd page aligned to the proper item description. I can do it but i have to do single sheet for every single one office. I want to do it from a consolidated sheet like the 1st sheet.