• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with External References

JonP

New Member
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?
 

Attachments

  • Jon\'s KPI Chart.xlsx
    12.9 KB · Views: 1
  • KPI Totals 2.xlsx
    508.5 KB · Views: 2
In short, SUMIF functions won't work on an external workbook.

If you wanted to continue with this setup, you'd need to query/pull the raw data into the KPI workbook, and then perform the SUMIF on them. IMO, the real problem is in having 2 workbooks. Much better to have a single workbook, and just use more worksheets.
 
Thanks Luke.
If SUMIF can't be used with external references then that explains it - if only I'd realised sooner it would have saved me hours!
The reason for multiple workbooks is due to the fact that any one workbook can only be accessed by one person at a time but each member of staff potentially needs to have their own KPI workbook open at the same time.
I had wondered about pulling the raw data across and then working with it but hoped I could avoid this.
Thanks again for your help.
 
Back
Top