How do I auto populate rows based on one cell value.

[tbucki1]

I need to auto fill 10 rows in Excel from a database based on one cell value. How do I do this? For example in Cell A1 I have the value "1993" and in A2 through A12 below I have empty cells. On a separate tab I have my list of data (See sample data below). In column A, I have a list of all the Cars, and in Column B I have the year. For example:

Car1 1993
Car2 1985
Car3 1955
Car4 2001
Car5 1993
Car6 1997
Car7 1999
Car8 1993
Car9 1967
Car10 1985

When I type in "1993" in Call A1 I want Car1, Car5, and Car8 to appear in the cells below. When I type "2012" in A1...Nothing pops up, because there are no cars with that year. When I type "2001" only Car4 pops up.

Without using VBA how can I build this so that the list is automatically updated each time I change the value of Cell A1? A2 through A12 should be dynamically populated based on A1 value.

Thanks!

 

[JLGWhiz]

This is Event Code for the worksheet change event. Any change on the worksheet will trigger the code but it will only execute the search, copy and paste when the change is in cell A1. To install the code, right click the sheet name tab of sheet 1, then click 'View Code' in the pop up menu. That will open the sheet code module. Copy and paste the code below into the large code pane, then close the VB editor. Save your workbook as a macro enabled workbook (.xlsm) to preseve the code when the workbook is closed. Make sure the icon for design mode is not highlighted and you are ready for business. if you enter a year in cell A1 of sheet 1, all matching items in columns A and B of sheet 2 will be listed down columns A and B of sheet 1.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, fLoc As Range, fAdr As String
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Set sh1 = Sheets(1) 'Edit sheet name - destination sheet
Set sh2 = Sheets(2) 'Edit sheet name - source sheet
Set rng = sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Range("A2", sh1.Cells(2, 2).End(xlDown)).ClearContents
    Set fLoc = rng.Find(sh1.Range("A1").Value, , xlValues, xlWhole)
        If Not fLoc Is Nothing Then
            fAdr = fLoc.Address
            Do
                fLoc.Offset(0, -1).Resize(1, 2).Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
                Set fLoc = rng.FindNext(fLoc)
            Loop While fAdr <> fLoc.Address
        Else
            MsgBox "Year not found!", vbInformation, "NO HIT"
        End If
Application.EnableEvents = True
End Sub

In case nobody can figure how to do it without VBA.

 

[ozbod]

Is there any reason that you wouldn't use the autofilter in the dataset, so that in column B you would be presented with all (populated) years and selecting the appriate year would only display those matching records ?

 

[tbucki1]

Thanks JLGWhiz,

I figured VBA was going to be my solution. This looks good! I forgot to mention if I wanted to be able to type in the word "all" into cell A1 and have it show all the cars how would I do that? So I could type in a year and get the cars that correspond to that year....or I could type the word "all" and have it pull the entire list. I assume it would just be one more line of code, correct?

Also I noticed on the code you had it pulling in both the car and year (column A & B), anyway to just show the cars and not the year?

Thanks so much!

 

[JLGWhiz]

This seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, fLoc As Range, fAdr As String
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Set sh1 = Sheets(1) 'Edit sheet name - destination sheet
Set sh2 = Sheets(2) 'Edit sheet name - source sheet
Set rng = sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp))
sh1.Range("A2", sh1.Cells(2, 2).End(xlDown)).ClearContents
    If LCase(sh1.Range("A1")) = "all" Then
        rng.Offset(0, -1).Copy sh1.Range("A2")
    Else
        Set fLoc = rng.Find(sh1.Range("A1").Value, sh2.Range("B2").End(xlDown), xlValues, xlWhole)
            If Not fLoc Is Nothing Then
                fAdr = fLoc.Address
                Do
                    fLoc.Offset(0, -1).Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
                    Set fLoc = rng.FindNext(fLoc)
                Loop While fAdr <> fLoc.Address
            Else
                MsgBox "Year not found!", vbInformation, "NO HIT"
            End If
    End If
Application.EnableEvents = True
End Sub

 

[JLGWhiz]

Is there any reason that you wouldn't use the autofilter in the dataset, so that in column B you would be presented with all (populated) years and selecting the appriate year would only display those matching records ?

@obod, No particular reason, just wrote the find method because it was the first thing to pop in my head.

 

[Rick Rothstein]

Assuming the list of cars and dates are on Sheet2 in the cells A1:B10, put this array-entered** formula in cell A2 of the sheet where you type in the date you want to search and copy down...

=IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF(Sheet2!B$1:B$10=A$1,ROW(A$1:A$10)),ROWS(A$1:A1))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[tbucki1]

Thanks Rick,

This worked! Assuming I want to add in the ability to type in the word "All" into Cell A1 and show all the cars, how would you modify the equation. I am assuming you would add in another IF statement? So the user has two options...Type in "All" and pull in the complete list of cars for all the years... or type in the year in cell A1 (e.g. 1993) and only pull in those cars specific to that year.

Thanks,

 

[Rick Rothstein]

This worked! Assuming I want to add in the ability to type in the word "All" into Cell A1 and show all the cars, how would you modify the equation.

Give this array-entered** formula a try...

=IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF((A$1="All")+(Sheet2!B$1:B$10=A$1),ROW(A$1:A$10)),ROWS(A$1:A1))),"")

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Rick Rothstein]

Give this array-entered** formula a try...

=IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF((A$1="All")+(Sheet2!B$1:B$10=A$1),ROW(A$1:A$10)),ROWS(A$1:A1))),"")

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

While I would personally use the above formula solution, for those who would want to use a VBA solution, this is how I would write the Change event code to do it...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Long, X As Long, CarList As Variant, Result As Variant
  If Target.Address(0, 0) <> "A1" Then Exit Sub
  CarList = Sheets("Sheet2").Range("A1:B" & Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim Result(1 To UBound(CarList), 1 To 1)
  For R = 1 To UBound(CarList)
    If Target.Value = CarList(R, 2) Or UCase(Target.Value) = "ALL" Then
      X = X + 1
      Result(X, 1) = CarList(R, 1)
    End If
  Next
  Target.Offset(1).Resize(UBound(Result)) = Result
End Sub