Data Validation from Table (Non-VB)

**Sleeper** · 08-24-2009, 03:45 PM

Hello everyone,

I'm having a brain block that I hope someone can help with. I'm building a workbook that has a tab for each department (sales, admin, tech, etc.) w/i a project. I also have an inputs and instructions tab where I have all of my lists and background info hidden.

Essentially, on each tab, the user will build a team for the project using drop down lists. The lists reference data on the inputs tab. This data includes a list of all of the titles in the company, as well as which department they belong to. I want the validation to be structured such that the sales tab drop downs only see sales titles, admin only sees admin titles, etc. I also need it to be dynamic in its drop down inclusions so that when I add/subtract titles, I don't have to reset the validation for new cell references. Below is an example of the what the reference data looks like. If anyone has any questions, let me know. Ideally, I'd like a solution that is non-VB. TIA for your help.

Input Tab:

Col A --- Col B
Dept --- Title
Sales --- Rep
Sales --- Sr. Rep
Admin --- VP
Admin --- SVP
Sales --- Closer
Tech --- Analyst
Tech --- Developer

**NBVC** · 08-24-2009, 04:00 PM

One Non-VBA way would be to automatically (using formulas) create lists for each department from your main list (these lists will extract respective department info only) and list them without blanks in their own columns... As you add/delete items from the main list, then these lists would expand/contract automatically.

Each of these lists would be named dynamically and then you would create validation on each sheet based on sheet name, so that proper data is displayed.

If you post sample workbook, then I will try to give you sample automation.

**Sleeper** · 08-25-2009, 01:31 PM

Here's a quick/dirty view of the workbook. Tabs for each department to build teams for projects; inputs tab containing the title roster, etc.

The user would build teams using drop downs on the highlighted lines. The drop downs would be department specific in that the sales drop downs would only see sales titles, admin sees only admin titles, etc.

The title list is tied to another program, so I cannot change its order to sort or differentiate between departments. That is why I've added the dept column.

I want to be able to maintain the inputs tab, and the tabs' validations to automatically update themselves, as opposed to having to update validation criteria on every tab, each time we have a title update.

Any help I can get would be great. I've done these setups in the past, but for some reason, the knowledge escapes me. Thanks.

**NBVC** · 08-25-2009, 01:45 PM

See attached,

Steps:

1. Formula in G1 of each sheet to Count matches of tab spec...

Please Login or Register  to view this content.

2. Formula in H1 of each sheet to extact corresponding items where matches happen

Please Login or Register  to view this content.

Note: This formula is confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down... Currently it accomodates entries in input sheet up to row 1000.

3. Define Dynamic ranges for each tab going to Formulas tab, Define Name, New Name

Name: Sales
Formula: =OFFSET(Sales!$H$1,0,0,COUNTIF(Sales!$H:$H,"?*"),1)

Name: Admin
Formula: =OFFSET(Admin!$H$1,0,0,COUNTIF(Admin!$H:$H,"?*"),1)

Name: Tech
Formula: =OFFSET(Tech!$H$1,0,0,COUNTIF(Tech!$H:$H,"?*"),1)

4. Create Data Validation

Select all cells in Sales sheet that need apply lists...Go to Data tab, Data Validation, Select List from Allow menu and enter formula =Sales

Repeat for other tabs.

**Sleeper** · 08-25-2009, 05:23 PM

That'll work. Thanks a lot. If it's of any interest to others, I modified a bit. I placed steps 1 & 2 onto my Inputs sheet, so they are all together. This changed the "Countif" portion of the Offset formulas a bit as well. Thanks again, NBVC.