Fill Dashboard Table based on Criteria "OrderNumber"

%3CLINGO-SUB%20id%3D%22lingo-sub-2166177%22%20slang%3D%22en-US%22%3EFill%20Dashboard%20Table%20based%20on%20Criteria%20%22OrderNumber%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2166177%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20look%20through%20all%20the%20suggestions%20but%20couldn't%20find%20any%20suggestion%20or%20solution%20for%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Excel%20Workbook%20with%20two%20sheets%20and%20in%20the%20first%20one%20it%20contains%20a%20table%20that%20it%20is%20populated%20by%20an%20External%20Link%20via%20SQL%20that%20loads%20the%20desired%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20second%20sheet%20I%20have%20another%20table%20that%20acts%20as%20a%20dashboard%20where%20it%20has%20only%20the%20columns%20of%20the%20info%20that%20really%20matters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20based%20on%20the%20order%20number%2C%20list%20all%20the%20rows%20(dynamically)%20from%20he%20original%20table%20into%20each%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20change%20the%20order%20number%2C%20the%20info%20would%20change%20accordingly%20the%20information%20on%20the%20original%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS%3A%20I'm%20using%20Office%202010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-02-25_16-07-37.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257911i866EB6AF7ECFBCD9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%222021-02-25_16-07-37.png%22%20alt%3D%222021-02-25_16-07-37.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2166177%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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.

 

2021-02-25_16-07-37.png

 

3 Replies

@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

 

@Hans Vogelaar 

 

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

@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.