# Advanced Data val

1. ## 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.

2. ## Re: Advanced Data val

Hello can you upload a sample book

3. ## Re: Advanced Data val

Here we goForumHelpData Val.xlsx
Originally Posted by hemesh
Hello can you upload a sample book

4. ## Re: Advanced Data val

Like this !!!

5. ## Re: Advanced Data val

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???

6. ## Re: Advanced Data val

open name manager, select open_jobs

INDEX('Job Costing'!\$D\$2:\$D\$7,MATCH("Open",'Job Costing'!\$M\$2:\$M\$7,0)):INDEX('Job Costing'!\$D\$2:\$D\$7,MATCH("Open",'Job Costing'!\$M\$2:\$M\$7,0)+COUNTIF('Job Costing'!\$M\$2:\$M\$7,"open")-1)

you need to adjust \$d\$7\$ to the let's say \$D\$500 and \$m\$7 to \$m\$500 in whole formula That will do the thing

7. ## Re: Advanced Data val

i have adjusted the formula !!!

8. ## Re: Advanced Data val

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

9. ## Re: Advanced Data val

As stated in workbook for this formula to work properly either all open should be first or last. If that is not possible then you need to use helper column
posting helper column file in a moment

10. ## Re: Advanced Data val

Try this !!! I have kept name Helper in name manager As D1:D100 you can increase and decrease it as per your need

manage your vlookup according to helper column if you plan to do so to retrieve the actual value

11. ## Re: Advanced Data val

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?

12. ## Re: Advanced Data val

where is AMEX and LLPartners in your data

13. ## Re: Advanced Data val

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

14. ## Re: Advanced Data val

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

15. ## Re: Advanced Data val

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

Regards

16. ## Re: Advanced Data val

Many thanks for all your help.
Originally Posted by hemesh
Find the attachment! Use only unique values for Open Jobs!!

Regards

17. ## Re: Advanced Data val

Hello alfgrey you are welcome and thanks for the feedback
if your query is solved mark thread as solved and click" * " add rep icon in the bottom left corner of my post If I have helped you

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1