Data validation list using filtered data?

[thommo41]

Hi,
I've just discovered data validation lists in Excel (I know - where have I been!). They are really going to help with some forms I've created, but I need to filter the list based on some source data.
My source data is basically a current job list. So each row is a job record, columns such as Job Number, Client Name, date etc. There is also a field called Active. We have numerous active jobs, marked with an 'X' or 'Yes' or similar.
I want my data validation list to show Job Numbers from my source data (easily done), but only where that job number is marked as active.

I'm sure this is easier than I realize, but I'm struggling to figure the best way of doing this without complicating matters with lots of code.

Thanks in advance, Alan

 

[thommo41]

OK, I finally found a post (I got so many hits I had to ask the question - sorry!) that's the same problem. My issue with this though, despite the fact it works, is that the person answering the question is obviously a genius, and I'm not. I've no idea how or why it does what it does!
http://www.mrexcel.com/forum/excel-questions/460039-data-validation.html

Andrew Poulsom - if you're out there, can you explain? Or can anyone else dechipher the complexities of the answer? In the mean time, it works, perfectly!

 

[Andrew Poulsom]

What don't you understand?

 

[thommo41]

Morning Andrew. I'm back in the office, and what you put together is way beyond how my mind works! But reading it over and over, I can see what you did in the first couple of formulas.
However, the bit where you define the name for the data validation list? It's got me puzzled!
Thanks, Alan

 

[thommo41]

Actually Andrew, I think I've got my head around it. Thank you for being a genius, my world needs people like you!