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 differe...
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.
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.
- It's a weird problem; I hadn't seen it before and I've referenced closed workbooks thousands of times before. The problem seems to have solved itself (some how).