Wrapping Indirect around a Filter

[Mountlodge]

Hi All,

=CHOOSECOLS(FILTER(Tbl_Extract[[PROVISION]:[Column3]],(Tbl_Extract[PROVISION]="")*(Tbl_Extract[CATEGORY]="Plan")*(Tbl_Extract[LEVEL2_CATEGORY]="")*(Tbl_Extract[Column3]<>"cancelled")),29)

The above formula returns the expected result which is great. Rather than displaying the list (14 rows) I'd like to put this into a single cell as a Filter, then let the user select whichever of the values returned.
Initially I tried using List and in Source just use the formula but didn't like this. So tried wrapping the above formula around Indirect, again no luck.

If I thought the number of rows stayed static then I could hard code the cell address into the Source in the Filter but as Tbl_Extract row dimensions changes almost daily this isn't feasible. As far as I'm aware when referring to a Table in XL you don't need to refer to a sheet which If I were using ranges I assume I would need to include. So, kinda out of ideas. Maybe Source doesn't like Tables ??. Googled this and couldn't see anything on it. As always, any thoughts on a solution would be gratefully received.

TIA.
John

 

[Cubist]

You can't directly put an array inside the List validation, but you can output the result in the cells first, say A1, and then do this in the validation:

='Sheet1'!A1#

 

[Mountlodge]

You can't directly put an array inside the List validation, but you can output the result in the cells first, say A1, and then do this in the validation:

='Sheet1'!A1#

Ha!! Now that's clever. Thanks Cubist.

 

[Cubist]

You're welcome.