I have a spreadsheet file with one tab holding monthly data (large file with 300,000 rows and 55+ columns). This file is protected with a password to 'modify' and saved on a local network drive for several co-workers to access. We have saved it two different ways, one as a Power BI table name, and another version with a standard named range (to test if either works different)
I created a separate Excel file, saved on a network location but different folder. I inserted a Pivot table to perform ad hoc analysis of the data. I've tried both methods, either the Table name or the Named Range as the data source of the pivot table. Both work fine, when I have both the master data file and the 2nd pivot table file opened.
During a typical month, the Master Data table and file is updated and re-saved with new data by another co-worker. Once it's updated I want to open my 2nd file with the pivot table and just hit Refresh, without opening the main data file. When I do that I get an error saying "We couldn't get data from "NAMED RANGE" in the workbook..."Folder\file.xlsx". Open this workbook in Excel and try again"
However.... if I change the data source in my Pivot Table, 2nd file, to reference the Entire columns of the master file (ie: folder\filename.xlsx!$A:$BC ) the Pivot Table will refresh with no problems when the Master Data is closed. I've been told not to reference entire columns or rows as it uses too much memory, so a named range is better. We add columns periodically, along with new rows each month, so the data range will change.
Can I link a 2nd file to a master file with a Named Range / Table Name and have it update/refresh without opening the master data file?
Thanks!
I created a separate Excel file, saved on a network location but different folder. I inserted a Pivot table to perform ad hoc analysis of the data. I've tried both methods, either the Table name or the Named Range as the data source of the pivot table. Both work fine, when I have both the master data file and the 2nd pivot table file opened.
During a typical month, the Master Data table and file is updated and re-saved with new data by another co-worker. Once it's updated I want to open my 2nd file with the pivot table and just hit Refresh, without opening the main data file. When I do that I get an error saying "We couldn't get data from "NAMED RANGE" in the workbook..."Folder\file.xlsx". Open this workbook in Excel and try again"
However.... if I change the data source in my Pivot Table, 2nd file, to reference the Entire columns of the master file (ie: folder\filename.xlsx!$A:$BC ) the Pivot Table will refresh with no problems when the Master Data is closed. I've been told not to reference entire columns or rows as it uses too much memory, so a named range is better. We add columns periodically, along with new rows each month, so the data range will change.
Can I link a 2nd file to a master file with a Named Range / Table Name and have it update/refresh without opening the master data file?
Thanks!
