set drop-down values based on vlookup

Question

I have a worksheet UserEntry with 2 columns, Block and Address. I want to validate both of these based on another worksheet Validation with the same column names. The data on the Validation sheet is as follows:

Block | Address
---------------
001   | 101
001   | 101.3
001A  | 35
020-1 | 203
020-1 | 203.5
020-1 | 204.1

...

There are about 11000 different blocks, and about 40000 block/address pairs.

My goal is that if a user enters a value into the Block column on the UserEntry sheet, the drop-down choices in the Address column change to correspond with that Block.

I tried using Custom validation with this formula:

=VLOOKUP(UserEntry!A2,Validation!A2:B40000)

But that evaluated to an error. I saw some solutions in various forums that involved setting named ranges and then having the VLOOKUP() search for the appropriate named range, but it seems like that won't work here because I'd have to create 11000 named ranges.

How can I make the validation drop-down for Address include all the values corresponding to a given Block value?

Answer 1

You can use a dynamic named range for this.

Assumptions:

1. Validation list is sorted A..Z
2. Validation list is in range Validation!A:B
3. Validation list includes headings in row 1
4. On sheet UserEntry the Address cell to be validated is one cell to the right of the ented Block
5. Tried and tested on Excel 2010

Create a Named Range to use as validation source (I've used name ValList):

=OFFSET(Validation!$B$1,MATCH(INDIRECT(ADDRESS(CELL("row"),CELL("col")-1)),Validation!$A:$A,0)-1,0,COUNTIF(Validation!$A:$A,INDIRECT(ADDRESS(CELL("row"),CELL("col")-1))),1)

Add Data Validation to the required cells: Allow List, Source =ValList

1. Uses INDIRECT, ADDRESS and CELL to get a refernce to the user entered Block relative to the active cell
2. Uses MATCH and COUNTIF to get the position and size of the matching Blocks in the validation list
3. Uses OFFSET to set the return range to the addreesses matching the enterd block

Answer 2

You didn't mention VBA but here is a solution that uses it.

Step 1

Create a master table of Block-Address relationships. Make sure this is sorted on Block. I used Sheet1:

Cell E2 is important. You don't actually have to put anything there, but the macro will use it. Cell E3 is for show only, but you will use the formula (which is commented out here so you can see it) momentarily.

Step 2

Create a named range. The formula in Refers to: is what you saw in E3 above, and you can see the reference to cell E2 here. The formula for your convenience is

=OFFSET($A$1,MATCH($E$2,$A:$A,0)-1,1,COUNTIF($A:$A,$E$2),1)

Step 3

Set up a new worksheet (Sheet2) where the data entry will happen. Create data validation for the Address column as shown.

Step 4

Open the VBA editor and paste this code in the module for Sheet2. You can remove the Debug statement if you wish. Again note the reference to cell E2 on Sheet1:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Debug.Print "fired on " & ActiveCell.Address
  If Not Application.Intersect(Target, Range("B:B")) Is Nothing Then
    Sheets("Sheet1").Range("E2").Value = ActiveCell.Offset(0, -1).Value
  End If
End Sub

Step 5

Enjoy. Your data validation is now context sensitive. Examples: