Forum Discussion
External data referencing returns #REF! error
I am working on some spreadsheets with my company's expenses and financial information. One workbook, 2022 Expenses PivotTable, has information on all expenses, the income, and salaries. In a different workbook, 2022 Pro Forma, I am referencing information from the former workbook into the Pro Forma due to the complexity of how my boss wants it set up.
At one point, I can't remember when exactly, all of the formulas in the 2022 Pro Forma returned a #REF! error; I checked the formulas and there isn't a #REF! in it (which would've resulted from deleted reference cell). When I opened the 2022 Expenses PivotTable to see if there was a problem in that workbook, I noticed that all the #REF! errors in the Pro Forma were suddenly fixed. (I've attached an image of the #REF! error from Excel online since my Excel app is too slow right now from a company wide SharePoint sync; the problem appears on both applications though)
I understand that this can happen is the Trust Center settings and/or macro settings aren't configured properly. I have added my entire C Drive to the Trust Center to try and fix this problem but it didn't work.
Can someone PLEASE help me with this!? I shouldn't have to open both spreadsheets to view the information on just one of them.
Unrelated note:
While searching for an answer, I came across this post from January of 2023 asking the same question. https://techcommunity.microsoft.com/t5/excel/reference-link-to-external-workbook-field-value-using-path-with/m-p/3715403/highlight/true#M176894
Since there was no response to ScholiSG 's post, I'm tagging them in this one in hopes that we will BOTH get an answer!
attached an image of the #REF! error from Excel online since my Excel app is too slow right now from a company wide SharePoint sync;
If too slow,I guess you need another B/S architecture to share data.- The reason it was slow was because over 20K files had to sync due to folder restructuring lol
LilYawney Which function(s) did you use to connect the two workbooks? In case you used INDIRECT that explains the REF errors when the other file isn't open.
From the support notes on INDIRECT:
- I didn't use INDIRECT. This is the formula that I use: =SUMIFS('2022 Expenses PivotTable.xlsx'!Income[Amount],'2022 Expenses PivotTable.xlsx'!Income[In. Stmnt. G/L Groups],$B$8,'2022 Expenses PivotTable.xlsx'!Income[Date],E$7,'2022 Expenses PivotTable.xlsx'!Income[SBU],$B$9)
When the other file is closed, the reference sheet does turn into a url link to the file.LilYawneyCan't really tell. Did some tests myself with SUMIFS to a closed file. Didn't cause any problems.
