I have about 20 different spreadsheets that will be used for data entry. Unfortunately it isn't feasible to put all the data entry into a single spreadsheet. These data entry sheets are my source workbooks. For simplicity sake, the source workbooks will have three columns: A B and C.
I have created a summary workbook that summarizes the data in Column C of the 20 source workbooks. I used a sumifs formula to take the sum of C every time A and B equal a certain value.
The summary workbook works fine when and only when I have the source workbooks open. But if the source workbooks are not open, then the formula in the summary workbook returns a "#Value!" error.
Is there any way to make formulas in the summary to work without having the source workbooks open? Or is it the case that formulas with external references will not work unless the source workbooks are open? Maybe it depends on the function I'm using?
Update: It seems that formulas with index and match will work even if the source workbook is closed. The problem is that I can't think of a way to get an index match function to do what I need here. I only want C cases where A and B equal a certain value. (There will only be one case where A and B equal a certain value. I figured Sumifs is the best function here but maybe there is an alternative?)
I have created a summary workbook that summarizes the data in Column C of the 20 source workbooks. I used a sumifs formula to take the sum of C every time A and B equal a certain value.
The summary workbook works fine when and only when I have the source workbooks open. But if the source workbooks are not open, then the formula in the summary workbook returns a "#Value!" error.
Is there any way to make formulas in the summary to work without having the source workbooks open? Or is it the case that formulas with external references will not work unless the source workbooks are open? Maybe it depends on the function I'm using?
Update: It seems that formulas with index and match will work even if the source workbook is closed. The problem is that I can't think of a way to get an index match function to do what I need here. I only want C cases where A and B equal a certain value. (There will only be one case where A and B equal a certain value. I figured Sumifs is the best function here but maybe there is an alternative?)
Last edited: