Drop down list from filtered data

[petrcensky]

Hello. I am just trying to create a drop down list from column with filtered data. The problem is that the drop down list always shows ALL data from the column, ignoring the filter. Any ideas?

[FDibbins]

Hi, welcome to the forum

Perhaps create a 2nd list, based on the filter, then use that for the DD?

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[petrcensky]

Example: worksheet.xlsx

Older unsolved thread on the same topic: http://www.excelforum.com/excel-gene...e-visible.html

[MarvinP]

Hi petrcensky and welcome to the forum,

Here are the instructions..

https://support.office.com/en-us/art...rs=en-US&ad=US

The secret is to filter and select those filtered cells. THEN click on Find & Select and the GoTo.. and then the SPECIAL button in the bottom of the Go To dialog.

I've always wondered how to do this... Now we both know.

[petrcensky]

Well, the process must be iterative and automatic :-( The drop down list should be dynamicaly changed with every new selection in the column with data.

[MarvinP]

OK petrcensky,

Watch out what you ask for... Find the attached with a Dynamic Named Range called ItemsDNR that will grow and shrink depending on how many cells have data. This is your Validation List.
It also has some Event Code that is triggered when the second sheet is clicked on.
The main module copies the visible cells in column A from sheet1 and pastes them into the DNR range on sheet2.

It is now completely "iterative and automatic". BUT it is much more complicated.

[petrcensky]

Wow, MarvinP - thatīs awesome! Thatīs exactly I wanted! Amazing - Thank You!!!!