Advanced Data val

so another forum member helped me out today with this formula =OFFSET(JobNum,0,0,COUNTIF(JobNumCol,">""")) which only displays job numbers in a dropdown list from the JobNumCol range. As the cells in that range contain formulas we had to use this formula so the list would ignore the cells with formulas in them and provide a dymanic list. This works great!!

Now, the adjacent cell to each of the Job numbers in the JobNumCol range state either "Open" or "Closed" Is there a way to expand the formula =OFFSET(JobNum,0,0,COUNTIF(JobNumCol,">""")) to only list job numbers in the JobNumCol range that are "open" only?

Any help would be greatly appreciated.

Here we goForumHelpData Val.xlsx

Originally Posted by hemesh

Hello can you upload a sample book

Originally Posted by hemesh

Like this !!!

Many thanks for your post. I might be doing something wrong but when adding more "open" jobs the list doesnt update to include them.

Example ForumHelpData Val.xlsx

As i said i may have done something wrong???

Originally Posted by hemesh

i have adjusted the formula !!!

Thanks for your reply, its solved half the problem. Ive now made jobs 4, 6, 8, 10 etc open but the dropdown lists 4,5,6,7,etc ie its listing closed jobs?

ForumHelpData Val (1).xlsx

Thankyou so much! Too advanced for me on my own! I wonder if it can be taken a step further?
Can the drop down not only display the open jobs but show the Job name next to the number I.e
J-1001 AMEX
J-1004 LLpartners
etc
The reason is its easier to pick the correct job number when you can see the job name. The complicated part is that lets say i select J-1001 AMEX only J-1001 populates the cell. My guess is this cant be done?

Originally Posted by hemesh

Hello alfgrey, Please upload a workbook of sample data with possible output. and of course that can be done.
Regards

Try thisForumHelpData Val (2).xlsx

Many thanks

Many thanks for all your help.

Originally Posted by hemesh

Find the attachment! Use only unique values for Open Jobs!!


Regards