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) wit...
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.