Drop down lists

[MadHatsJess]

Hello,

Wondering if someone can help me please.

I am pulling together a dashboard.

I have a table of data that is too large for the dashboard. I want the table to sit in another sheet and pull through to the dashboard.

The idea I have is to have a drop down list, when I select an option I want the table to populate.

For example

List
Shop 1
Shop 2
Shop 3When I select shop 1 from the list I want only the rows for shop 1 to populate the table.

I would greatly appreciate any help

Jess

 

[mgrady11]

You can use a Data Validation to create a drop down list, then base the formulas on that specific tab to calculate based on your selection.

Excel Drop Down Lists - Data Validation

 

[L. Howard]

and Hi Jess,

I read your request as having a drop down in a cell, say the cell is B2, and when you make a selection in B2, a list of items from another sheet appears on the sheet starting in a designated cell.

So something like... Shop 1 is selected in B2. The list appears in C2:C25. Now you select Shop 3 in B2, the previous list is deleted and replaced with the data from Shop 3, which may be only C2:C5 (a short list). And the same for each Shop ? selected.

If that is true, then you can use a change event macro to make that happen.

Here is such a code, I suspect you would need help setting up the data formatting for the Shop 1, 2, 3 etc. and the macro to work with the Shop data.

Howard

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Dim rngFound As Range
Dim aRowCount As Long, _
    aColumn As Long, _
    tRowCount As Long, _
    tColumn As Long
    
Dim myFnd As String
myFnd = Target

Application.EnableEvents = False

   tColumn = Target.Offset(, 1).Column
  
   tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
   
   If Target.Offset(, 1) Is Nothing Then
     '
     Else

     Target.Offset(, 1).Resize(tRowCount, 1).ClearContents

   End If
    
Set rngFound = Sheets("Sheet2").Range("E1:K1").Find(What:=myFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not rngFound Is Nothing Then

     aColumn = rngFound.Column
     aRowCount = Sheets("Sheet2").Cells(Rows.Count, aColumn).End(xlUp).Row
     aColumn = rngFound.Column

    rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)
  
 Else

    MsgBox "No match found."
    [B2].Activate
    Application.EnableEvents = True
    Exit Sub

End If

[C2].Activate
Application.EnableEvents = True

End Sub