Help! Data Validation and Nesting If Statements

jsutter · Mar 7, 2012

I'm working on an HR database for applicants. I need to create 2 drop-downs, the second dependent on the other.

The first one lists the status of the interviewing process, such as "Interviewed" and "Not Interviewed". This range has been named InterviewingStatusQuery. I further named the individual fields Interviewed and Not_Interviewed.

If they answered one of the above 2 items, I need a separate dropdown in the next column to elaborate as to why. I've created a named range for each the 2 review questions, named InterviewedReviewQuery and NotInterviewedReviewQuery.

The question is how to set up the if statement within the data validation tool to do the second column.

Here's what I've come up with:

=IF($J2=Interviewed,InterviewedReviewQuery,(
IF($J2=Not_Interviewed,
NotInterviewedReviewQuery,
"N/A")))

This comes back with only blanks. Help?

BigC · Mar 8, 2012

You can use the IF function to achieve stacked/dependent DV ranges, but a shortcut/simplrer method is to use the INDIRECT function instead.

In the 1st DV range, enter the Defined Name ("InterviewingStatusQuery") of the range containing your 1st set of options, as you've mentioned.
However, you now want a second set of Defined Names whose names match exactly the options available in the 1st DV range (i.e. you can't have "Not interviewed" as a DV option, and a Defined Name of "Not_interviewed", as they're not exactly the same.) Excel is particular about what constitutes a valid Defined Name (e.g. no spaces), so you almost have to make these the priority, with how the options look in your DV drop-down as secondary.
The formula in your 2nd DV range should be =INDIRECT([1st DV range]) which in your case I think will be =INDIRECT(InterviewingStatusQuery)

INDIRECT works by interpretting what it takes as an argument (e.g. "Interviewed") as an object reference (i.e. sheet, range, cell, etc), so if "Interviewed" is selected in the 1st DV range, this value is returned as the argument to INDIRECT in the 2nd DV range which will then look for a Defined Name called "Interviewed" and return the values contained in that range (your explanations).

HTH

jsutter · Mar 8, 2012

OK, here are the fields:

 <table class="MsoNormalTable" style="width:437.0pt;margin-left:5.15pt;border-collapse:collapse;mso-yfti-tbllook: 1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="0" cellpadding="0" cellspacing="0" width="583"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes;height:30.75pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;mso-border-alt: solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.75pt" width="145"> Interviewing Status
</td> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-left: none;mso-border-top-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.75pt" width="145"> Status DV Names
</td> <td colspan="2" style="width:219.0pt;border:solid windowtext 1.0pt; border-left:none;mso-border-top-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.75pt" width="292"> Review Determination
</td> </tr> <tr style="mso-yfti-irow:1;height:30.0pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-top: none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" width="145"> Call For More Info
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> CallForMoreInfo
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> Interviewed
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="147"> Not Selected for Interview
</td> </tr> <tr style="mso-yfti-irow:2;height:15.0pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt; border-top:none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" width="145"> Contacted
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" nowrap="nowrap" width="145"> Contacted
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145"> In-Process
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="147"> Need Sponsorship
</td> </tr> <tr style="mso-yfti-irow:3;height:15.0pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-top: none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" width="145"> Under Review
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145"> UnderReview
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145"> Not Fit-Skills
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="147"> No Opening
</td> </tr> <tr style="mso-yfti-irow:4;height:30.0pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-top: none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" width="145"> Interviewed
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> Interviewed
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> Not Fit-Personality
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="147"> Non-Responsive
</td> </tr> <tr style="mso-yfti-irow:5;height:30.0pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-top: none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" width="145"> Not Selected for Interview
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> NotInterviewed
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="145"> Took Another Job
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.0pt" width="147"> Not a Fit-Skills
</td> </tr> <tr style="mso-yfti-irow:6;height:30.75pt"> <td style="width:109.0pt;border:solid windowtext 1.0pt;border-top: none;mso-border-left-alt:solid windowtext .5pt;mso-border-bottom-alt:solid windowtext .5pt; mso-border-right-alt:solid windowtext .5pt;background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt; height:30.75pt" width="145"> Interns Grad 12/12 and Beyond
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#F2DCDB;padding:0in 5.4pt 0in 5.4pt;height:30.75pt" width="145"> StillAttending
</td> <td style="width:109.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.75pt" width="145"> Too Much Money
</td> <td style="width:110.0pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-bottom-alt:solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt; background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:30.75pt" width="147"> Relocation
</td> </tr> <tr style="mso-yfti-irow:7;height:15.0pt"> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:110.0pt;border-top:none;border-left:solid windowtext 1.0pt; border-bottom:none;border-right:solid windowtext 1.0pt;mso-border-left-alt: solid windowtext .5pt;mso-border-right-alt:solid windowtext .5pt;background: #E6B8B7;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="147"> Too Much Money
</td> </tr> <tr style="mso-yfti-irow:8;mso-yfti-lastrow:yes;height:15.0pt"> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:109.0pt;padding:0in 5.4pt 0in 5.4pt;height:15.0pt" width="145">
</td> <td style="width:110.0pt;border:solid windowtext 1.0pt;mso-border-alt: solid windowtext .5pt;background:#E6B8B7;padding:0in 5.4pt 0in 5.4pt; height:15.0pt" width="147"> Took Another Job
</td> </tr> </tbody></table>

I Added the "Status DV Names" field from what I'm understanding from your Indirect() explaination. The question now is, how do I connect the drop-down options from the InterviewingStatusQuery (Column A) to the list of their field names for the DV in Column B?

Columns C & D are grouped together as ReviewStatusQuery, then further broken down:
* InterviewedReviewQuery (Column C) is the drop-down that I want for the selection of "Interviewed"
* NotInterviewedReviewQuery (Column D) is the drop-down that I want for the selection of "Not Interviewed".

Could you provide me with a bit more insight into how to structure this?

BigC · Mar 8, 2012

As mentioned in my last post, the Defined Names (your col B) for the various 2nd level options must match exactly the options available in the 1st level DV cell. So, given that you've already created the names in col B, what you need to do now is replace the values you have in col A (and which must be included in the dimensions of the range defined as "InterviewingStatusQuery") with those in col B.

Then, when the user selects "NotInterviewed" from the drop-down in the 1st DV cell,
=INDIRECT(InterviewingStatusQuery) in the DV setup for 2nd DV cell will return:
=NotInterviewed to that cell, which will instruct Excel to look for the range defined with the name "NotInterviewed" and in turn offer the values in that range (col D) to the user in the drop-down to the 2nd DV cell.

The Defined Name "Interviewed" must refer to the range of cells shown in col C containing the 2nd level options to be offered when a user selects "Interviewed" in the 1st DV cell.
The Defined Name "NotInterviewed" must refer to the range of cells shown in col D containing the 2nd level options to be offered when a user selects "NotInterviewed" in the 1st DV cell.
This works like a drill-down: select an option and it expands to further options.

You don't need the Defined Names "ReviewStatusQuery", "InterviewedReviewQuery" & "NotInterviewedReviewQuery".

What do you want to happen when the user selects one of the other four options in the 1st DV cell (e.g. "CallForMoreInfo", "Contacted", etc.)?
If nothing, I suggest you enter "N/A" in a separate cell, and then assign each Defined Name for these four options to that cell (you can have more than one name referring to the same cell/s), so that when one of these options is selected in the 1st DV cell,
=INDIRECT(InterviewingStatusQuery) in the DV setup for 2nd DV cell will return, say:
=Contacted to that cell, which will then instruct Excel to look for the range defined with the name "Contacted" and in turn offer the value "N/A" to the user in the drop-down to the 2nd DV cell (as it will if one of the other three options are selected.

As you can see, this method offers some advantages over using stacked IF functions in the DV setup, but it also has some disadvantages.

Smitty · Mar 8, 2012

Welcome to the Board!

Excel MVP Zack Barresse has a good Dependent Data validation article here, including links to several other tutorials and a working example you can download.

HTH,

BigC · Mar 8, 2012

Thanks for the lead Smitty.

From a brief read, this looks a little complicated to set up and is not as intuitive, but is much more dynamic & scaleable, and overcomes the confusion/limitations regarding Defined Names encountered when using the INDIRECT method as more levels are added.

Cheers

Help! Data Validation and Nesting If Statements

jsutter

New Member

Excel Facts

BigC

Well-known Member

jsutter

New Member

BigC

Well-known Member

Smitty

Legend

BigC

Well-known Member

Similar threads

Forum statistics

Share this page

Help! Data Validation and Nesting If Statements

jsutter

New Member

Excel Facts

BigC

Well-known Member

jsutter

New Member

BigC

Well-known Member

Smitty

Legend

BigC

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock