1. ## Excel: Dynamic Range

I have a list of entries (call it "Entries") like this:

101.01
101.02
102.01
201.01
201.02
201.03
214.01
214.02
300.01
300.02

When I enter a 3-digit number in cell \$A\$1, I need to return the range of values that start with that number. So entering "201" will give me the range that includes 201.01, 201.02, and 201.03.

This will be used in a combo box that is created in a macro, so I can use either a worksheet formula or VBA code.

Thanks!

2. This offset formula entered as an array should do it:
=OFFSET(Entries,MIN(IF(INT(Entries)=\$A\$1,ROW(Entri es)))-ROW(Entries),0,COUNT(IF(INT(Entries)=\$A\$1,1)),1)

Steve

3. Can you enter an Array in a Data Validation list?
Can you make an Array into a named Range?

4. You can in datavalidation add that formula to the "source" of a List.

You could also make a named formula (named range is a little of a misnomer to me) with the formula as the "refers to" and you use that name as the source in the list.

[The advantage I find of using the named formula is that when in the name manager, you can test what range it evaluates to.]

Steve
PS the formula and datavalidation require no coding or macro warnings

5. Shawn,

Here is a solution using VBA. Entering a number in Cell A1 will trigger the Worksheet_Change event to run the code and compare values

HTH,
Maud

MatchedNumbers.png

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
'DECLARE  AND SET VARIABLES
Dim rng As Range
Dim cell As Range
Set rng = Range("A4:A13")
Row = 4
Value = Int([a1])
'-------------------------------------------------
'CHECK IF SELECTION IS MULTIPLE CELLS OR CELL A1 THEN
'COMPARE VALUES AND PLACE IN MATCH LIST
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
Else
Range("D4:D13").ClearContents
For Each cell In rng
If Value = Int(cell) Then
Cells(Row, 3).Value = cell
Row = Row + 1
End If
Next cell
End If
End Sub```