Sep 06 2023 08:46 AM - edited Sep 06 2023 09:45 AM
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-p...
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!
Sep 06 2023 09:50 PM
@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:
Sep 06 2023 10:42 PM - edited Sep 07 2023 03:29 PM
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.
Sep 07 2023 07:35 AM
Sep 07 2023 07:37 AM
Sep 07 2023 07:51 AM
@LilYawneyCan't really tell. Did some tests myself with SUMIFS to a closed file. Didn't cause any problems.
Sep 14 2023 07:28 AM