Question When Linking Multiple Source Workbooks

huskerfanatic

New Member
Joined
Mar 8, 2016
Messages
1
Hello,

I'm a newbie here, hoping someone can provide a little insight. I've combed through existing threads and found absolutely nothing about the issue we have. I'm using Excel 2013 (MS Office Professional Plus) on Windows 7 Enterprise.

We use upwards of 500 workbooks in our budgeting process. Each workbook is specific to a certain department and location which may or may not exist. These source workbooks flow into a location summary workbook (destination). The issue arises if someone at the location creates a workbook that is ultimately not needed and inputs values. Say someone then opens up the destination workbook which updates its values based on the extraneous source workbook. Those values remain in the destination workbook even once the unneeded source workbook is deleted. Our formulas in the destination look something like this: =IFERROR('\\File Path\[100GF1617-100000.xlsm]Dept Totals'!$J26,0).

We use IFERROR as some fields, but not many, have text instead of values which causes a #VALUE! error. The "100" and "100000" in the file name are changeable based on the location or department. We have always manually checked to ensure that each department with values in the destination file exists as a source file. If not, we create the source with all zero values and allow the destination file to update accordingly. My question is this: Is there a way to tell Excel, either with a formula or macro, to populate the appropriate cells with zeros when the source file in question does not exist, or must this always be a manual process?

I hope that made sense and certainly let me know if clarification is needed. Thanks so much!

-Nick
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,390
Members
430,425
Latest member
xlsee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top