Forum Discussion
Multiple drop downs
Hi, I am trying to simplify finding information in a table with many columns. I have a course tracker where participants names are in the first column (A2-A20) the next column is course name (B1) with date taken B2-20, next column is date of expiration with dates in C2-C20 and repeats for 15 courses. I have inserted 2 drop downs one being participant, and the second being course name. These drop downs are side by side under the table, am I able to have the 3rd cell pull the expiration from the above table according to what I've chosen in drop down 1 and drop down 2 and change as those selections change?
Absolutely! You can achieve this in Excel using either formulas or a combination of formulas and data validation. Here are two methods:
Method 1: Using VLOOKUP and OFFSET Functions:
Formulas for Participant and Course:
- In the cell displaying the participant letter (assuming C2), enter:
Excel=INDEX(Participants,MATCH(B2,Courses,0))- This formula finds the participant's letter based on the chosen course number from the second dropdown (B2).
- Copy the formula in B2 (course name dropdown) to the cell displaying the course number.
Formula for Expiration Date:
- In the cell showing the expiration date (assuming D2), enter:
Excel=VLOOKUP(OFFSET(INDEX(Participants,MATCH(B2,Courses,0)),0,-2),CourseData!A:C,3,FALSE)- Explanation:
- OFFSET: Navigates two columns to the left from the participant's letter (obtained from INDEX and MATCH) to reach the expiration date column.
- INDEX(Participants,MATCH(B2,Courses,0)): Similar to the formula in C2, it retrieves the participant's letter based on the chosen course number.
- VLOOKUP: Searches for the participant-course combination (constructed using OFFSET) in the expiration date column of your data table (adjust "CourseData" and column index if needed).
- 3: Specifies the column index for expiration dates in your data table.
- FALSE: Ensures an exact match for participant and course combination.
Method 2: Using Data Validation and VLOOKUP:
Create Named Ranges:
- Go to the "Formulas" tab and click "Define Name."
- Define a named range called "Participants" for your participant list (A2-A20) and "Courses" for your course names (B1).
Set Up Dropdowns:
- Select the cell where you want the first dropdown (participant). Go to the "Data" tab and click "Data Validation." Choose "List" from the "Allow" dropdown and enter the formula =Participants. Repeat this for the second dropdown (course name) using the formula =Courses.
Formula for Expiration Date:
- In the cell showing the expiration date, enter:
Excel=VLOOKUP(C2&B2,CourseData!A:C,3,FALSE)- This formula is similar to the VLOOKUP in method 1, but it concatenates the participant letter (C2) and course number (B2) before searching for the expiration date in your data table.
Note: Both methods achieve the same outcome. Choose the one that suits your preference and adjust the formulas and references as needed for your specific data layout.
This should allow you to dynamically display the expiration date based on your dropdown selections, simplifying your data analysis and information retrieval.
Do these work if I have multiple expiration date columns though? Every other column is an expiration date
You're absolutely right! The basic approach still works even if you have multiple expiration date columns, but we need to modify the formula slightly to accommodate them. Here's how:
Adjusted Formula:
Excel=INDEX($C$2:$F$20, MATCH($A3, $A$2:$A$20, 0), MATCH($B2, B$2:B$20, 0) + MOD(COLUMN($C2) - COLUMNS($C:$B), COUNTA(B$2:B$20) + 1))Explanation:
- The main change is in the third argument of INDEX:
- MOD(COLUMN($C2) - COLUMNS($C:$B), COUNTA(B$2:B$20) + 1): This part dynamically calculates the correct column offset based on the selected course and the position of the expiration date column.
- COLUMN($C2): Gets the column number of the first expiration date cell (C2).
- COLUMNS($C:$B): Calculates the number of columns between the participant and course columns (excluding B).
- COUNTA(B$2:B$20): Counts the number of courses (excluding the header).
- MOD(...): Takes the remainder of the previous calculation divided by the number of courses plus 1. This ensures the offset cycles correctly through the expiration date columns for each course.
- MOD(COLUMN($C2) - COLUMNS($C:$B), COUNTA(B$2:B$20) + 1): This part dynamically calculates the correct column offset based on the selected course and the position of the expiration date column.
- The rest of the formula remains the same, retrieving the value from the appropriate row and column based on participant and course selections.
Steps to implement:
- Update the formula in the desired cell with the adjusted version above.
- Ensure the cell references for your dropdowns ($A3 and $B2) and table ranges ($C$2:$F$20 and $A$2:$A$20) are correct.
- Adjust the range for expiration date columns ($C$2:$F$20) if you have more or fewer columns.
With this modification, the formula should dynamically adapt to your multiple expiration date columns, always displaying the date associated with the chosen participant and course.
- The main change is in the third argument of INDEX:
