Data Validation Using Multiple Named Columns

[HeatherProGro]

I am building a workbook for tracking jobs. The first column is the contractor name. There are a standardized list of these, but sometimes they add a one-off name to this list. The next column is the job name (see example data below). Each job name will be unique. All the data is entered into a table.


Contractor Job Name

Hopfauf C - Finish

Edgewood 305 Oak St - TP

Edgewood 305 Oak St - FD

Residential Jones

Sattler A - TP

Front St B - MD

Hopfauf KRAMER

MBS BUILDERS MART

Hopfauf TOBACCCO PREV

weeda mtl slabs

eds painting 11276 Winding Ridge


Here's the scenario. In another sheet of the workbook, I want the user to be able to choose the Contractor name from a drop down list of the unique contractor names available. I do not want a list of everything in the column since there will be duplicates of many of the names and there will eventually be hundreds of rows of data.


Next, I want the user to choose the Job Name from a drop down list of the unique jobs only for the contractor chosen in the previous cell. I found on chandoo.org a great formula that get me almost all the way there [=OFFSET('Master Database'!$B$2,MATCH($C$3,Contractors,0),0,COUNTIF(Contractors,$C$3),1)]


Unfortunately, what this returns is the correct number of choices (if I choose "Hopfauf", I get 3 choices of job names), but it doesn't show the CORRECT three choices (it only takes the first 3 choices next to the first instance of "Hopfauf"; C - Finish, 305 Oak St - TP, 305 Oak St - FD).


Wise and wonderful chandoo.org community, help me find my way! I confident it can be done, but I cannot seem to find an answer on my own. Thanks in advance!

 

[SirJB7]

Hi, HeatherProGo!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Does this help?

http://chandoo.org/forums/topic/data-validation-list-automatically-shows-the-first-item-in-the-list


Regards!

 

[HeatherProGro]

I have researched and looked through as many posts as I can think of to solve the problem before I resorted to posting a question on the forums.


The link you suggested works great IF you have regularly defined items that are in discreet columns. Since the information I want to pull is all in the SAME column, this fix does not work for me.


Here is a link to my data that should be easier to understand than what I was asking in my original post: https://www.dropbox.com/s/748sxwj7pczxk9b/Chandoo%20Example.xlsx


My Master Database is where the job information is entered.


The Job Label Form is what I'm trying to get working properly. C5 is my problem child. What I'm trying to do is choose the Contractor name in C3. Optimally, I will be able to do this by showing a list of all the possible unique contractors without duplicates directly from the Master Database list. Right now I'm using the column defined in the Data tab, but it will miss any one-off entries.


The goal is once the Contractor name is chosen, C5 is then populated with all the Job Names associated with that Contractor name. Right now, I can get the correct NUMBER of Job Names associated with Hopfauf (3), but the correct Job Names aren't showing - it shows the first three Job Names after the first instance of Hopfauf in the list.


Any help is greatly appreciated. Thanks!

 

[Sajan]

Hi Heather,

There was a recent post regarding multiple dependent validation lists:

http://chandoo.org/forums/topic/dynamic-unique-and-multiple-dependent-date-data-validation


(That was for dates, etc. but a similar approach should work for you.)


If that does not help you, I can look at your specific situation later today or tomorrow.


Cheers,

Sajan.

 

[HeatherProGro]

Sajan,


Again, this does not do what I need. Using the same data as in your example, I'd want to select "Area A" on the first dashboard selection cell, then have it populate all the dates associated with "Area A" in the second dashboard selection cell. I appreciate your help!

 

[SirJB7]

Hi, HeatherProGo!


Give a look to this link:

http://chandoo.org/wp/2012/10/04/formula-forensics-no-030/


Check the Haseeb A's formula for creating a unique ranked list from a list with duplicate values, use this technique for both Contractor and JobName, and set data validation for cells C3 and C5 of worksheet "Job Label Form" to this new unique lists (remove data validation for cell C6 too).


Regards!

 

[NARAYANK991]

Hi Heather ,


In case your problem has not yet been resolved , let us take it one step at a time ; the uploaded file contains the DV list for C3 ; is this OK ?


https://www.dropbox.com/s/q9g3n4nld07xxtn/Chandoo%20Example.xlsx


If it is , what is the next step ?


Narayan


P.S. The formula for excluding duplicates is from here :


http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/

 

[HeatherProGro]

I've solved the problem by making a hidden pivot table with a visible slicer. This allows the user to choose the Contractor name without duplicates and allows for a drop-down list that shows the correct Job Names associated with the chosen Contractor. Thanks for everyone's help!