Creating A Data Validation List Showing only Filtered Data

[RogerH]

I have a data validation list that works fine, however, when the data is filtered I want the validation list drop down box to only show the filtered items.

Example:

Sheet "Inventory" - Shows items in inventory (which the user can filter.)

Sheet "Sales" - This is where the validation list is located, taking the items in the Inventory worksheet.

I want the items in the Sales drop down validation list to only show filtered items in the Inventory list. I do not want to use a macro to do this.

Thanks!

[Jakobshavn]

Hi Roger:

Here is an extremely simple example. Column C has the data we want for the DV list, but only the filtered items. We set up a "helper" column (column D that reflects only those visible items. The DV list is used to fill cell E16.

[RogerH]

Thank you but I am quite confused. In the attachment I have rows D16 through D34 populated with #NAME?. When I filter the list in
column C to only show one entry (the "2") when I click on the data drop down box in cell E16 all I get is a long list of #NAME?.

Sorry if I am missing something here, I am kind of new at filtering in Excel.

[Jakobshavn]

My error, for Excel 2000, use this version.