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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

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