Drop Down List came from filtered table

[marksevilla]

Hi Excel Master,

Can you help me to create a Vba code or excel formula that create a drop down list from a filtered table.

Example from attached file.

I have Choose customer that auto filtered a table.
After that i choose port pairing, it must be drop down list of unique visible value from filtered table.
To be continue to another category.

 

[Peter Bartholomew]

I have only ever done something similar to this once.

On that occasion I ditched Autofilter and instead used the Advanced Filter to extract unique lists for drop down or to populate combo boxes.

Then I extracted the matched records to a new table and rebuilt the validation list from that. I arranged that I could apply the filters in any order so being able to clear a criterion was also important.

 

[marksevilla]

Hi Peter,

Can you do your given solution on attached excel file.
Currently my excel have auto filter based on customer name

 

[p45cal]

I think you're using Excel 2013 so you should be able to convert the table on the table sheet to a proper Excel Table, and when that's done you can add slicers, and move them to the other sheet.
As you select items in one slicer, items in other slicers appear/disappear. Clear a slicer's choices by clicking on the red cross at top right of each slicer.
You can also choose multiple items from each slicer with the standard combinations of Shift/Ctrl and click.
The table on the table sheet will be filtered accordingly.
No code.
You have some completely blank rows in your table; if you delete these then you won't see (blank) as a choice in the lists.
See attached.

 

[marksevilla]

Hi P45cal,

Thank you for given suggestion

From your given slicer. can you vlookup from data selected from slicer? i give you the full excel data of my calculator. Can you check if your given solution is applicable from my data

 

[bosco_yip]

1] Formulas added in helper list in Column W , X and Y.

2] In C1 to C5, dropdown list changed with dynamic range name

3] see attached file.

Regards
Bosco

 

[marksevilla]

Hi Bosco,

Thank you very much this exactly what i need. because my table is always updating with additional MA.

Can you create a formula for customer name with unique value from the table. for column V

 

[bosco_yip]

Hi Bosco,

Thank you very much this exactly what i need. because my table is always updating with additional MA.

Can you create a formula for customer name with unique value from the table. for column V

Here's the revised no.2 file being used formula to create a unique list for the Customer name in Column V.

Regards
Bosco

 

[p45cal]

Hi P45cal,

Thank you for given suggestion

From your given slicer. can you vlookup from data selected from slicer?

In the attached slicer values go into cells C1:C4. It's a bit of a cheat. You should go with Bosco's solution.

 

[marksevilla]

In the attached slicer values go into cells C1:C4. It's a bit of a cheat. You should go with Bosco's solution.

Hi P45cal,

Thank you for new learning i think i can used you given idea to other scenario.

Thank you

 

[marksevilla]

Here's the revised file being used formula to create a unique list for the Customer name in Column V.

Regards
Bosco

Hi Bosco,

can you review my formula on cell G10


my formula can't read the value on C3 because its on text format.
I try to convert it into number but the filter formula return a different value

 

[bosco_yip]

Hi Bosco,

can you review my formula on cell G10
View attachment 55557

my formula can't read the value on C3 because its on text format.
I try to convert it into number but the filter formula return a different value

1] Revised has done as per requested.

2] See attached revised no.3 file.

Regards
Bosco

 

[marksevilla]

quote="marksevilla, post: 238653, member: 53105"]Hi Bosco,

Thank you very much this exactly what i need. because my table is always updating with additional MA.

Can you create a formula for customer name with unique value from the table. for column V

Thank you Bosco