Now i can copy\rename file as i wish and had no issues at all. Click 'Options' button (left side of ribbon in my Excel), switch to Data tab and uncheck 'Save source data with file'. Activate 'PIVOTTABLE TOOLS\ANALYZE' ribbon. Refresh pivot table.ĭo this with every PivotTable in workbook.Ģ) How to get rid of this nasty issue for good: Put carrent inside textbox (do not use arrow keys!) and delete file name including surrounding characters. Click ANALYZE and Change data source button. able to locate that cell, and it produces a REF error in the spreadsheet. The names begin in cell H5 and the total numbers begin in cell L91. The pivot table data changes monthly or semi-annually so that throws off the cell reference when I tried to use that method.
After simple file copy\rename and opening copied\renamed file, excel started complaining about security (external data source) and i was unable to refresh PivotTables (message 'cannot open PivotTable source file xxx' where xxx was the old file name)Īctivate pivot table (put cursor inside) so that 'PIVOTTABLE TOOLS' ribbon is shown. A REF error (the ref stands for reference) is the message Excel displays. The pivot table displays a total number for about 50 different names, and I need to use these totals on my other sheet. I had this very issue (using Excel 2013).
