Vlookup or datavalidation, but exclude some criteria (file and PDF attached)

Hello everyone!!

I'm a noob to the forum but an avid Excel user.

Question - how can I use Vlookup or data validation, but exclude the return value if the lookup cell is "99?"

I have a To Do list (will try to attach here) with priority numbers (1,2,3) and also completed numbers (99) for each task. In a second tab I have my daily five, where I want to set up a "drop down" (i'll put the validation or vlook up results here) to pick the top five I'll do for that day. This saves me the copy and paste from the first tab. The first tab has "99" for all complete tasks and I need to keep them so I know what I have done. The other tasks and priority 1, 2, 3 to help me manage.

When I click the cell to add a task to (see PDF), how can I limit my list that I can add from to ONLY the rows with 1 or 2 or 3 for a priority, essentially NOT including the 99? The "full list" tab of all the to do items has an active filter so I can sort on task or priority or whatever and see if a task is done or what category, etc... I'd like to keep this sort ability without losing the "drop down" ability in the "top five."

Hope I didn't confuse you.

Thanks

Kirk Thompson

Hi

You can get a validation list with the following formula (drag down)

Formula:

Please Login or Register  to view this content.