Custom validation list with conditions

[meph]

Hello Excel Experts!

I am trying to create a validation list, with its source data being filtered from another sheet of the same workbook. I also need to validate the drop list against some conditions in the source table.

Here is an example of the data am trying to create`(I want to create the droplist for the column Contract):

"Contract Definition" Sheet or Table:
-----------------------------------
Company Contract Service Report End date: Status
--------- --------- -------------- --------- ------
CompanyA ContractA1 Service 1 31/12/2014 Active
CompanyA ContractA2 Service 2 31/12/2014 Active
CompanyB ContractB1 Service 1 31/12/2014 Inactive


"Service Reports" Sheet or Table:
--------------------------------
Company Contract Service Report
--------- --------- ----------------
CompanyA ContractA1 Service 1
CompanyA ContractA1 Service 2
CompanyA ContractA2 Service 3



In the Service Reports Sheet, i want to create a list for each Contract, based on the specified Company and on the Status field, as defined in the Contract definition table.

Thanks!

[hemesh]

Hello Meph Welcome to the forum can you please post a workbook
To attach a workbook go to advanced, add file, select file and upload and done

[meph]

Hi hemesh,

Here is the file I am working on:

Workbook1.xlsx

In sheet "Service Reports", I want that the contract field displays only the contracts related to the related company and which are still active and whose expiry date is not attained yet.

Appreciate your help !!

[BeachRock]

Hi meph,

Take a look at the file I posted here; Example-Filters.xlsm. It has a good example of dependant menus in it using data validation.

[meph]

Hi Tony,

Many thanks for your assistance, the Excel file you posted was of BIIIG help!

I am almost done with my Excel: I am now (proudly) able to restrict the Contracts list to the selected company. I still need to add a couple of more restrictions to the logic regarding the contracts status and expiry date. I should be done with that by tomorrow and will update the tread accordingly.

I never thought I would be asked to use these 'complex' Excel formulas!! (complexity being a subjective point of view )

Cheers!

[FDibbins]

Hi and welcome to the forum

I am almost done with my Excel:

good luck with thinking you are "almost done". Excel becomes very addictive, and once you have mastered 1 thing, and hear/find out about another - the learning process starts all over again The more you know, the more you realize you dont know

[SDCh]

.... and once you have mastered 1 thing, and hear/find out about another - the learning process starts all over again The more you know, the more you realize you dont know

Good quote for me, this not only for excel

[meph]

I was quite thrilled when I managed to make the Custom validation list work as per BeachRock's suggestion. That was a big leap for my work

However, I am getting frustrated again by 2 obstacles:

1- The 'master' data (in my case, it is the Contracts table) is not a static list and will not always be alphabetically sorted. In other terms, Contracts might be created for any Company and appended to the end of the master data list at any time. If the list is not sorted properly, the Contracts combo box might show contracts not related to specified company. From what i understood from the function and from what i read in the forum, that seems to be a restriction caused by the combination of the functions used. Is that correct or can this be corrected somehow?

To make my case clearer, my data is designed like so:

• A Company can have multiple Contracts.
• Each contract can have several Service Reports.
• To create a Service Report, the user has to choose a Company from the 'master' combo box and a Contract from the contracts combo box

2- My second question is: How do I add a constraint to my validation? i want to filter contracts that are only Valid ? A column indicates the status of each contract (Valid or Expired).

(BeachRock, i tried to reverse engineer your file, but i faced the fact am not that skilled :D )

Here is the file I am working on :

[BeachRock]

Hi meph,

I also need to figure out a way to automate the process of adding new categories for choosing and adding new sub categories to existing categories in such a way that makes it easy to do on the fly. The example workbook I posted for you was just the beginning of a project I'm working on for my job. Unfortunately, I don't have time until later next week to work on that part due to my necessity to turn in an IT budget for 30 buildings due by Wednesday. I have an idea in mind but I think it's going to take a while to figure out. I'd be happy to share what I figure out with you though.

Are you on a time constraint?

However, I wanted to pass on to you that if auto filters won't constrain your data as you need in your 2nd item above, the workbook I posted for you also has a great multiple category filter method contained in it. You can use it to bypass the limitation of autofilter having only two categories. Click this link to watch the YouTube video where I learned it and then maybe reverse engineering my workbook for the filters will make more sense.

I added a more recent version of my project too. Much farther along. Take a look at my Lists sheet. This is where my data is pulled from. Same idea as yours but I'm not creating a page for each category. The color coding helps tremendously. I think it might be easier to add the ability for categories and sub-categories to be manipulated with them this way.