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. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 328 Times in 321 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

  4. #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.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,198
    Thanks
    14
    Thanked 328 Times in 321 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

  6. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,083
    Thanks
    39
    Thanked 186 Times in 173 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
  •