Data Validation - Creating a Dropdown List with Offset to separate ranges

[danielrussell2]

Hello all,

Hopefully everything is posted in the correct area and I hope I can get my issue across intelligently.

I'm creating a "Job Setup" sheet that will refer backwards and forwards to other sheets in this workbook.

The scope of the work is doors - interior vs exterior, door types, etc.

The issue is that I'm trying to make a drop down list on my "Job Setup" sheet for the "Door No." column where the selections possible depend on the column before it, the "Door Int or Ext?" column (where the dropdown allows a choice of "INT" or "EXT"). The range for the possible selections dependent on the "Door Int or Ext?" column is on another sheet, "DR RCP", and they are broken up (not all in one continuous list, as that page has an "interior" and "exterior" breakout so you can see your interior doors vs your exterior doors more easily). So, if the door is an interior door, on the "Job Setup" sheet, a user would select "INT" from the dropdown list, and in the next column ("Door No."), I want only the interior door numbers from the "DR RCP" sheet to be available. And, naturally, if it is an exterior door, user would select "EXT", and I would want only the exterior door numbers to be available on the drop down list.

I started trying to nest IF and OFFSET functions in the Data Validation box, but am getting nowhere fast.

"Job Setup" Sheet - Need C70 to have a dropdown list that only shows options from B37:B49 on the "Door RCP" sheet (because B70 has been selected as INT).

"Door RCP" Sheet - this is where the door numbers are input, exterior doors under "Exterior" (B12:B34) and interior doors under "Interior" (B37:B49). Dependent on jobsetup sheet B70 selection, I need only the corresponding door numbers to be available on a drop down list (exterior or interior door numbers)


Hopefully this all makes sense and one of you kind folks can help me out.

Thanks in advance!

 

[MarkMacka]

Hi Dan,
I have found this works. If you set up your Drop down box list on a separate page using "IF" formulas instead of text,

=IF('Job Setup'!B70="EXT",'DR RCP'!B12,'DR RCP'!B37)
=IF('Job Setup'!B70="EXT",'DR RCP'!B13,'DR RCP'!B38)
=IF('Job Setup'!B70="EXT",'DR RCP'!B14,'DR RCP'!B39)
=IF('Job Setup'!B70="EXT",'DR RCP'!B15,'DR RCP'!B40)
=IF('Job Setup'!B70="EXT",'DR RCP'!B16,'DR RCP'!B41)
=IF('Job Setup'!B70="EXT",'DR RCP'!B17,'DR RCP'!B42)
=IF('Job Setup'!B70="EXT",'DR RCP'!B18,'DR RCP'!B43)
etc.
This should default your door number dropdown list to Interior numbers if nothing or "INT" is in cell B70and show your Exterior dropdown list if Cell B70 is "EXT"

 

[danielrussell2]

I cannot believe how easily I just found a solution - logged back on to show it here, and just saw your reply @MarkMacka. Thank you for your input, and I did try it - the only issue I had with that approach is that the possible number of interior doors and exterior doors is different, so it would keep copying down the column and giving incorrect choices based on whether Interior or Exterior was chosen.

What I tried (DUH, *slaps self on forehead*) was putting an IF function in the data validation source box:
***Note - on the DR RCP sheet I had already named the ranges for interior doors and exterior doors (InteriorDoorList and ExteriorDoorList, respectively)

Choose cell C70 on "Job Setup" sheet, Data Validation, choose list, in the source box enter the formula below:
=IF(B70="EXT",ExteriorDoorList,InteriorDoorList)

Boom. Simple and concise. Works perfectly.

 

[danielrussell2]

In addition, if you do not want blanks to show on the drop down list (which is especially annoying because the list will default to show the first blank, and you will have to scroll up to find your options), using an offset and counta nested function works to only show the options you have with no blanks. I just nested these inside my IF function

=IF(B70="Exterior",OFFSET('DR RCP'!$B$12,0,0,COUNTA(ExteriorDoorList),1),OFFSET('DR RCP'!$B$37,0,0,COUNTA(InteriorDoorList),1))