Forum Discussion

djvas89's avatar
djvas89
Copper Contributor
Feb 25, 2021

Fill Dashboard Table based on Criteria "OrderNumber"

Hello,

 

I've look through all the suggestions but couldn't find any suggestion or solution for my problem.

 

I have a Excel Workbook with two sheets and in the first one it contains a table that it is populated by an External Link via SQL that loads the desired information.

 

In the second sheet I have another table that acts as a dashboard where it has only the columns of the info that really matters.

 

My goal is based on the order number, list all the rows (dynamically) from he original table into each column.

 

If I change the order number, the info would change accordingly the information on the original table

 

PS: I'm using Office 2010.

 

 

excel
Formulas and Functions

3 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 25, 2021

    djvas89 

    The following column headers on the Dashboard sheet do not occur on the Montblanc sheet, so code to copy data will fail.

     

    N° of client order
    Position
    Delivery date (Exit Bradco)
    Qty
    Price
    Currency

     

    • djvas89's avatar
      djvas89
      Copper Contributor
      Feb 25, 2021

      HansVogelaar 

       

      That Excel is a dummy file. It's what I've been testing on.

       

      If the columns match on either side it is possible to do?

       

      Can you tell \ hint me on how to do it?

       

      Thanks

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Feb 25, 2021

        djvas89 

        Make sure that each of the column headers in D1:Q1 on the Dashboard sheet exactly matches a column header in row 1 of the Montblanc sheet.

        And the column header in B1 must also match the column on which you want to filter exactly. For example Nº of intern order.

         

        Right-click the sheet tab.

        Select 'View Code' from the context menu.

        Copy the following code into the worksheet module:

         

        Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        On Error GoTo ExitHere
        Worksheets("Montblanc").UsedRange.AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Range("B1:B2"), _
            CopyToRange:=Range("D1:Q1")
ExitHere:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

         

        Switch back to Excel.

        Save the workbook as a macro-enabled workbook (.xlsm).

        Make sure that you allow macros when you open the workbook.