Forum Discussion
Dynamic Output Based on Dropdown Selection With Corresponding Range V2
Initial problem
Creating a dynamic output based on the selection from dropdowns in cells B19 and B22 when the same criterion was chosen. In simpler terms, when one range is filled, its content is copied to another range, when the corresponding dropdown menu's criteria are met.
The context is an exercise program, in which the athletes need a way to track their previous performance measured by reps, sets, and RPE the last time they used a specific exercise.
See picture below for reference.
This was solved by:
=IFERROR(IF(B22="","",INDEX(O$19:Q21,SEQUENCE(3,,XLOOKUP(B22,B$19:B19,SEQUENCE(ROWS(B$19:B19)),"N/A",0,-1)),SEQUENCE(,3))),"")
V2
I want to expand this functionality to five different dropdowns, dynamically.
Corresponding range = green cells in the picture below
Dropdown list B19 with corresponding range =O19:Q21
Dropdown list B22 with corresponding range =O22:Q24
Dropdown list B25 with corresponding range =O25:R27
Dropdown list B28 with corresponding range =O28:Q30
Dropdown list B31 with corresponding range =O31:Q33
The problem lies in creating a dynamic Excel formula that not only searches for the most recent occurrence of the selected option from any dropdown but also considers multiple dropdowns meeting the same criteria.
It should be configured such that when an option is chosen from a dropdown (for instance, in cell B25), it should search for the last instance where that same option was selected in the previous dropdowns (such as B19 and B22).
Then, it should display the data associated with the most recent selection (in this case, the data from the range O22:Q24 for B22) into the new corresponding range aligned with the current selection (which would be U25:W27 for B25).
Here's the layout, five dropdowns all meeting the same criteria. Green-colored cells are the range of which the performance is inputted while the purple-colored cells are for the records of previous performance.
3 Replies
- Relic24 I would like to help, unfortunately, it's not really clear what the expected end result should be for me. Could you perhaps share / attach an Excel file with the starting point (V1)?
Hi LouisDeconinck ,
Apologies for the delay.
I have included the workbook with two sheets: one containing V1 in sheet 1 and the V2 layout in sheet 2. Additionally, I've included annotations and text boxes to clarify the specific issues I need assistance with in sheet 2.Relic24 Just tested things and they seem to be working as intended with the current formula. You put the formula in cell J6, press enter. Then you copy cell J6 and paste in cells J9, J12 and J15.
The current formula uses an expanding reference, which means that you need to use this copy-paste technique for the ranges to automatically update.
=IFERROR(IF(B6="","",INDEX($G$3:I5,SEQUENCE(3,,XLOOKUP(B6,B3:B$3,SEQUENCE(ROWS(B$3:B3)),"N/A",0,-1)),SEQUENCE(,3))), "")
Those parts I bolded are expanding range references. The starting point stays fixed (through the $'s) while the ending point shifts down automatically to the following rows as you pull down / copy down. The -1 as the search mode argument in the XLOOKUP makes sure to search from last to first, meaning that the results are always from the last match.
Because of the copy-paste, the references will automatically change. E.g. this is how the formula will look like in cell J15:
=IFERROR(IF(B15="","",INDEX($G$3:I14,SEQUENCE(3,,XLOOKUP(B15,B$3:B12,SEQUENCE(ROWS(B$3:B12)),"N/A",0,-1)),SEQUENCE(,3))), "")Optional tip if this was helpful: [link removed by admin]
