Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #3
    New Lounger
    Join Date
    Aug 2013
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Can you enter an Array in a Data Validation list?
    Can you make an Array into a named Range?
    Last edited by ShawnVW; 2013-10-03 at 11:12.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    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
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •