Data Validation - using visible rows from filtered list

**anandvh** · 08-17-2012, 05:24 PM

Hi,

I am working on application and need help with a problem I am facing. I have attached a sample file to provide more clarity to my problem.

I have a list with 2 columns. Column A is the PO No. and column B is the status of the PO.

To enable the user to select a PO, I need to provide a drop down list (cell E12) that contains only the PO Numbers that are not closed. I tried to apply the filter to show only the open PO. However even with this, the drop down list in cell E12 list all the PO numbers including those with status "closed".

How can I get the data validation list in cell E12 to show only those PO numbers that do not have the status "closed" ?

Any help will be greatly appreciated.

Thanks in advance for your efforts.

Anand

**tigeravatar** · 08-17-2012, 05:40 PM

Anand,

Attached is a modified version of your provided workbook. In column I is a list of just the PO's that do not have "Closed" in column B. To get that list, in I2 and copied down is this formula:

Please Login or Register  to view this content.

Then I created a dynamic named range to pick up the just the list of Open PO's without any blanks. I named the range OpenPOs and defined it with this formula:

Please Login or Register  to view this content.

Then, I set the data validation list in cell E12 to:

Please Login or Register  to view this content.

Does that work for you?

**anandvh** · 08-17-2012, 06:02 PM

Thanks Tigeravatar for the efforts. This seems to be a solution. The only doubt that I have is whether the formula can be on another sheet so that it does not interfere with the main table

Anand

**tigeravatar** · 08-17-2012, 06:12 PM

You can put it on another the list on another sheet. It would look like this if it was in Sheet2 column A with the formula starting in A2 and copied down:

Please Login or Register  to view this content.

And then update the dynamic named range formula to reflect the new sheet name and column

**anandvh** · 08-17-2012, 06:14 PM

thanks tigeravatar. Tried out your solution and it seems to be working. I think I will try this out for now. I may come back to the forum if I run into any other problems when using this.

Anand