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
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
