Hi all,
I have 2 workbooks I am working on and am stuck on a question regarding pivot tables/ pivot charts.
In workbook 1 I have a Power Query that updates the workbook with ~20,000 rows of data.
In workbook 2 I am trying to create a dashboard containing a pivot table and pivot chart of the data from workbook (1) for general reporting purposes. I do not want to do this in workbook 1 because I am looking to host workbook 2 onto SharePoint as an "easy to read" file.
Currently when trying to create a pivot table in workbook 2 with the data from workbook 1 it works as long as when the connection is first made and that workbook 1 is still opened. The moment when workbook 1 and 2 is closed the connection seems to be lost when reopening workbook 1 and 2 and then trying to refresh the pivot table data.
When checking the data source of the pivot table in workbook 2 the table/range sections states "#REF!" instead of what the original source "[workbook1.xlsm]sheet1!$A$5:$X$50000"
Any suggestion or point in the right direction would be greatly appreciated!
I have 2 workbooks I am working on and am stuck on a question regarding pivot tables/ pivot charts.
In workbook 1 I have a Power Query that updates the workbook with ~20,000 rows of data.
In workbook 2 I am trying to create a dashboard containing a pivot table and pivot chart of the data from workbook (1) for general reporting purposes. I do not want to do this in workbook 1 because I am looking to host workbook 2 onto SharePoint as an "easy to read" file.
Currently when trying to create a pivot table in workbook 2 with the data from workbook 1 it works as long as when the connection is first made and that workbook 1 is still opened. The moment when workbook 1 and 2 is closed the connection seems to be lost when reopening workbook 1 and 2 and then trying to refresh the pivot table data.
When checking the data source of the pivot table in workbook 2 the table/range sections states "#REF!" instead of what the original source "[workbook1.xlsm]sheet1!$A$5:$X$50000"
Any suggestion or point in the right direction would be greatly appreciated!
