Filtered Data Validation List

[michaelkelly02]

I'm trying to create a simple data validation list that will change based on wether or not the data is needed.

In my example I have two columns of data. One for project names and one for status (active or inactive).

I want a data validation list of project names where only active projects names will show up on my list. That way as projects become inactive I can change the cell status to inactive and have the project no longer appear on my data validation list. I'm thinking there may be a formula that will lookup my project status and return only the active names for the list.

Project	Status
Project 1	Active
Project 2	Inactive
Project 3	Active
Project 4	Active
Project 5	Inactive

In this example my data validation list would only include Projects 1, 3 and 4

Does anyone know how to do this? I'm open to creative solutions if there is not an easy way to do this.

[Tony Valko]

You would have to extract the active projects to a new list then use the new list as the source for the dropdown.

Like this:

Data Range

	A	B	C	D
1	Project	Status	-----	Project
2	Project 1	Active		Project 1
3	Project 2	Inactive		Project 3
4	Project 3	Active		Project 4
5	Project 4	Active
6	Project 5	Inactive

This array formula** entered in D2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$6="Active",ROW(B$2:B$6)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, as the source for your dropdown list use:

=OFFSET(D2,,,COUNTIF(D2:D10,"?*"))

[michaelkelly02]

Thank you. I have no idea what I did here, but it works perfectly. I'll have to do some homework on INDEX

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[bengio]

Hi,

How would I amend this to filter for multiple values such as "Active", "New", "Anything" but not "inactive"

I tried turning the IF() function into an IF(OR()) but that returned every item in the list

Thanks
Marko

[FDibbins]

Hi,

How would I amend this to filter for multiple values such as "Active", "New", "Anything" but not "inactive"

I tried turning the IF() function into an IF(OR()) but that returned every item in the list

Thanks
Marko

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[RamRaj23]

Hello Tony,


You solution is excellent, and it worked.
I was looking for the same for one of my requirements. Incidentally, I found FILTER() function but it works only with Office 365 whereas, I have Home and Office 2019.
I took some time to understand your solution and worked out in bits which gave me a good understanding of the logic.

Good day!

Ram

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however you are not allowed to piggy-back another member's thread, so please open your own with a suitable title and, if you wish, a link back to this thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread