Result of Filtered Array (Excel 365) to appear as drop down list in data validation

Mandy_

New Member
Joined
Jan 29, 2021
Messages
36
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I have a filtered array result of text that appears in sheet 2. I want sheet 1 to display those results as a data validation list.

In the past I have used a table for the drop down list, but in that instance I had data that was manually entered, I wish to use data that will change dynamically.


Can a filtered array be added to a table? That would be my first preference - for all my formulas link to a table.
Otherwise how can I get the results of a filtered array to be available as a data validation list?

Thank you for your time,
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Not sure how you get your filtered list (FILTER() maybe?) or how your data is laid out on either sheet, but hopefully the XL2BB below will give you some clues...
Book1
ABCDEFGHI
1Full listFiltered listData validationHDR1HDR2HDR3
2a Firsta Firsta Seconddatadatadata
3a Seconda Seconddatadatadata
4b Firsta Thirddatadatadata
5b Seconda Firstdatadata
6b Third
7a Third
8
Sheet1
Cell Formulas
RangeFormula
C2:C4C2=FILTER(A2:A15,LEFT(A2:A15,2)="a ","")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
E2List=$C$2#
G5List=$C$2#
 
Upvote 0
Solution

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top