Hello folks, hopefully someone out there can help me with a problem I'm having using external references within a formula. I have been successfully using external references within formulas for a long time with no problems and in fact within the same workbook and to the same external sources.
In our company each member of staff (only 6) has an individual workbook onto which they enter various KPIs on a regular basis through the day, these KPIs are then taken by an IF formulas into a central workbook for use in a dashboard. All of the individual workbooks and the central workbook are stored in the same location and accessed via the local network in each case.
The problem has arisen when trying to add another KPI and trying to use the SUMIF formula within the central workbook to look at the same 'external' individual workbooks as before. everything works fine as long as the both workbooks are open on the same PC but as soon as the 'external' (individual) workbook is closed and all Links in the central workbook updated, all cells simply fill with #VALUE!
I have attached a copy of the central workbook (KPI Totals 2) and one of the individual workbooks (Jon's KPI Chart). As can be seen, the external references on the 1st worksheet work great but those on the 2nd worksheet won't work at all.
Please can anyone help with what is wrong?
In our company each member of staff (only 6) has an individual workbook onto which they enter various KPIs on a regular basis through the day, these KPIs are then taken by an IF formulas into a central workbook for use in a dashboard. All of the individual workbooks and the central workbook are stored in the same location and accessed via the local network in each case.
The problem has arisen when trying to add another KPI and trying to use the SUMIF formula within the central workbook to look at the same 'external' individual workbooks as before. everything works fine as long as the both workbooks are open on the same PC but as soon as the 'external' (individual) workbook is closed and all Links in the central workbook updated, all cells simply fill with #VALUE!
I have attached a copy of the central workbook (KPI Totals 2) and one of the individual workbooks (Jon's KPI Chart). As can be seen, the external references on the 1st worksheet work great but those on the 2nd worksheet won't work at all.
Please can anyone help with what is wrong?