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?
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.
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?
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
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
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
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
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
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:
The variables for the worksheet and table names are at the top of the code, so you can update those to suit.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
Let me know if that works...
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!
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.
Bookmarks