Forum Discussion

BACSec's avatar
BACSec
Copper Contributor
Jul 04, 2023

need help populate by drop down list

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.

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 04, 2023

    BACSec 

    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.

Resources