Log In
Register
Home
September 15, 2020
Offline
Hi
The code might look rather familiar.
How do I retrieve multiple items or periods using a DV drop down.
I want to retrieve/show periods 2 and 3 by selecting 2 and 3 in my DV range cell.
In this case, I connected Array Filter to Data Validation selection.
Thanks
Robb
See Answer
October 5, 2010
Offline
Hi Robb,
I journeyed down a few rabbit holes working on this as there are several ways it could be solved. In the end I decided to use some helper cells W3:W7 (in green) and modify the FILTER(CHOOSE()) formula you already had.
It ends up looking like a bit of a monstrosity but because we're changing text to numbers all the VALUE() functions have to be wrapped in IFERROR to avoid it breaking
=IFERROR (FILTER (CHOOSE ({1,2,3}, A2:A17353, I2:I17353, J2:J17353), ((A2:A17353=S2) * ((E2:E17353=IFERROR ( VALUE($W$3),"")) + (E2:E17353= IFERROR (VALUE ($W$4),"")) + (E2:E17353 = IFERROR( VALUE($W$5),"")) + (E2:E17353= IFERROR (VALUE($W$6), ""))))),"")
See attached file.
regards
Phil
Answers Post
September 15, 2020
Offline
Thanks Phiilip:
I Like the left, mid, right technique to see what's being pulled.
My only question is for One, Two, Three in DV, why helper column. I thought it will now retrieve One, Two Three from data based on multiple DV items separated by comma.
Regards
Robb
October 5, 2010
Offline
Hi Robb,
I used helper columns because I felt it was easier to split the text string (1,2,3) up in the sheet and then feed that into the array formula that pulls the data. Easier than doing all of that in VBA.
Phil
1 Guest(s)
