Avoid the need to open source workbook to see referenced data/cells in other workbooks

Hello everyone,

I need some advice. I run into this issue often and can't figure out how to fix it.

I have several workbooks that contain tables, graphs, formulas, etc. that reference data from a separate workbook. When the workbooks are all open at the same time, everything works great. But when the source workbook containing the data is closed while the workbook referencing it is open, the data does not show up.

I have a lot of different workbooks that my users access to see their performance graphs. The performance graphs are referencing data from my source workbook. As you can imagine this provides several advantages i.e. I only have to update one location of data, each individual workbook doesn't have to contain duplicated data, each workbook is not a huge file containing a ton of data.

If someone can advise me in the best practice for avoiding the need to open the source file to see referenced data I wold be very grateful.

Thank you,

- Justin

As far as I can remember, the source workbook has to be open.

I'm working on a similar thing. Effectively, what I'm doing is bringing the data into the Performance Dashboard worksheet (by way of VBA) and doing the calculations from that. The only visible tabs will be the Dashboard Tabs, all the others are hidden. This gives me a single point of reference for performance data.

This also has the advantage of ensuring you can't corrupt the 'operational' data. As part of the import, you can, if you choose, also list the date and, if necessary, time the data was imported (e.g. Performance Data sourced at mm/dd/yyyy hh:mm).

Apparently I have to rewrite all of my formulas to arrays!