Forum Discussion

Shane_P1730's avatar
Shane_P1730
Copper Contributor
Jun 07, 2023

Excel data extraction

Hi Community 

 

I am trying to establish a way where i can select Via a drop down or other way , select a "Profile/Position" that will also link its corresponding learning courses , that than will pull the finer course details from a large list and provide a simplified end assessment 

 

 

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Shane_P1730 

    To achieve this in Excel, you can use a combination of data validation, VLOOKUP function, and conditional formatting. Here is a step-by-step guide:

    1. Set up your data:
      • Create a table with the "Profile/Position" column, the corresponding "Learning courses" column, and the "Course details" column.
      • Fill in the table with the relevant data.
    2. Create a drop-down list:
      • On a separate sheet, create a drop-down list of the available "Profile/Position" options. You can use data validation to create the list.
      • Select a cell where you want the drop-down list to appear.
      • Go to the Data tab, click on Data Validation, choose "List" as the validation criteria, and select the range containing the "Profile/Position" options.
    3. Retrieve corresponding learning courses:
      • In another cell, use the VLOOKUP function to retrieve the corresponding learning courses based on the selected "Profile/Position" from the drop-down list.
      • The formula may look like this: =VLOOKUP(SelectedProfile,ProfileCourseTable,2,FALSE).
        • "SelectedProfile" is the cell reference of the drop-down list.
        • "ProfileCourseTable" is the range of your table containing the "Profile/Position" and "Learning courses" columns.
    4. Display course details:
      • Use conditional formatting to highlight or display the course details based on the selected learning course.
      • Create rules using formulas that compare the selected learning course with the course details in the table.
      • Apply the formatting or display the relevant course details when a match is found.

    By following these steps, you can create a dynamic system where selecting a "Profile/Position" from the drop-down list will automatically populate the corresponding learning courses and display the associated course details.

Resources