• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table: Losing Reference to external excel sheet when using Add this data to the data model

S

Sachin Bafna

New Member
I have created a pivot table referencing to an external excel sheet. This pivot table has been created with the option of "Add this data to the Data Model", so that I can get distinct count.

But now I am losing reference to the excel sheets for these pivot tables. When I check the Analyze -> Change Data Source --- I get the value of Table/Range as #REF.

Any idea how I can get around this problem as reference is lost everytime.
 
Unfortunately, you'd need to have source workbook open to have pivottable update, when you add to data model in the manner you described.

If you have access to PowerQuery (Get & Transform). Create connection to source table there, add it to data model and base your pivot table off of that data model. This will ensure connection is retained.
 
You must log in or register to reply here.
Back
Top