Results 1 to 6 of 6

Thread: Cannot SUM the value if connection by external source

  1. #1

    Cannot SUM the value if connection by external source



    Register for a FREE account, and/
    or Log in to avoid these ads!

    If I create Pivot table direct in the data1 excel file, the sum of value is correct.
    However, if I create connection by external source to the data1 excel file, it cannot sum the value.
    What's wrong? Pls kindly help.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	sum_of_month_no_calc.jpg 
Views:	43 
Size:	87.0 KB 
ID:	1050  
    Attached Files Attached Files

  2. #2
    The problem seems due to the null value in the beginning of the source data.
    If I cannot modify the data source and only can use connection by external data, how can I fix it?

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I've got to be honest here. There should be a way to make this work, but I haven't been able to crack it.

    Personally, at this point, I'd be extracting the data from the other file using VBA, pulling it into a table in your workbook, and summarzing it from there. This gives you the added bonus that you know have a localized table that you can manipulate to work around many of the other issues you've brought up.

    Are you interested in looking at that route, or is VBA a no-go?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Hi Ken,

    I'd like to know more how to extract the data from external excel file to workbook?
    Provided that it can refresh the data every time I open the workbook.

    Thanks.

    Sent from my phone using Tapatalk

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Here's what I'm thinking would need to happen:
    -Create an Excel table in your report file, and base your PivotTable off that
    -Built a macro that would:
    --> Clear all records in the table
    --> Open the other file
    --> Copy all records (which are not NULL) into your report table
    --> Close the other file
    --> Update your Pivot

    I can't look at it right now, but I may be able to spend some time on it tomorrow evening.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, so assuming the following:

    • The "remote" workbook, which holds your original data has the data on "Sheet1" starting in A1
    • The "report" workbook has:
      • A worksheet called "Data" to store the data table
      • A table on the worksheet starting in cell A1
      • The table is named "tblCurrent"

    Then you should be able to use this to update it:

    Code:
    Sub RetrieveList()
        Dim wbSource As Workbook
        Dim wsTarget As Worksheet
        Dim sTargetSheet As String
        Dim sFileName As String
        Dim sTable As String
        Dim lstTable As ListObject
        
        Application.ScreenUpdating = False
        
        'Set name of data table and worksheet that holds it here
        sTable = "tblCurrent"
        Set wsTarget = ActiveWorkbook.Worksheets("Data")
        Set lstTable = wsTarget.ListObjects(sTable)
        
        'Set name of the worksheet that holds the data in the remote file
        sTargetSheet = "Sheet1"
        
        'Clear the data in the table
        If lstTable.ListRows.Count > 0 Then lstTable.DataBodyRange.Delete
        
        'Open remote file
        sFileName = CStr(Application.GetOpenFilename)
        If sFileName = "Cancel" Then Exit Sub
        Set wbSource = Workbooks.Open(sFileName)
        
        'Copy data from remote to local file
        With wbSource.Worksheets(sTargetSheet)
            .Range("A1:A" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy
        End With
        lstTable.Range.Cells(1, 1).PasteSpecial xlPasteValues
        
        'Close the remote workbook
        wbSource.Close savechanges:=False
        
        'Sort the table to group blank rows
        With lstTable
            With .Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range(sTable & "[[#All],[Current Month Amount]]"), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .Apply
            End With
        End With
     
        'Delete blank rows
        lstTable.Range.AutoFilter Field:=1, Criteria1:="="
        With wsTarget
            .Rows("2:" & .Range("A" & .Rows.Count).End(xlUp).Row).Delete
        End With
        lstTable.Range.AutoFilter Field:=1
    End Sub
    The variables for the worksheet and table names are at the top of the code, so you can update those to suit.

    Let me know if that works...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •