[转]excel set drop-down values based on vlookup

本文转自:http://*.com/questions/10657811/set-drop-down-values-based-on-vlookup

问:

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 ... 答:
5down voteaccepted

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:

[转]excel set drop-down values based on vlookup

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)

[转]excel set drop-down values based on vlookup

Step 3

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

[转]excel set drop-down values based on vlookup

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:

[转]excel set drop-down values based on vlookup[转]excel set drop-down values based on vlookup

答:

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
上一篇:【动态规划/多重背包问题】POJ2392-Space Elevator


下一篇:Android UI 学习 自定义的布局 平滑移动 VelocityTracker()