Forum Discussion
Shane_P1730
Jun 07, 2023Copper Contributor
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
- NikolinoDEGold Contributor
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:
- 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.
- 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.
- 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.
- 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.