I have a spread sheet that has been created to pull in information from 31 other workbooks just to sum the information in the summary workbook. I've discovered Excel cannot cope with a very large formula that points to a number of other workbooks. This is what the formula looks like:
=IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C202-ESPA DATA EVIDENCE & PARTNERSH.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C100-DIGIMAP NATIONAL SERVICES.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C201-DIGIMAP STREAM SCHOOLS OS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C101-JORUM+.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C104-ACCESS MANAGEMENT.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C314-LOCKSS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C322-ABLE EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C000-JISC CORE FUNDING.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C330-GECO - JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C317-PECAN EJOURNAL ARCHIVE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C329-LINKED DATA - JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C319-JUNCTION OPEN ACCESS REPOS JIS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C328-MANTRA DATA MGMT - EDINA-JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C102-SDSS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C309-PEPRS EDINA.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C321-GEO SOILS E-FRAMEWORK.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C331-DIGITAL MONOGRAPH.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C103-MEDIA HUB JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C334-SHARED OPENURL DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C333-IGBIOS GEOSPATIAL JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C341-MEDIA HUB ENGAGEMENT-EASE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C342-MOBILE EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C338-USED - USABILITY ENHANCE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C335-GOLD- GOGEO LINKED DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C343-HARDWARE REPLACEMENT JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C339-SONEX EXTENSION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C336-SUNCAT OPEN METADATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP(C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C337-SHIBBOLETH CONSORTIUM.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C332-STEEV GEOSPATIAL JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C358 - SAFENET.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C340-REPOSITORY NET JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C356 - KEEPERS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C346-DISCOVER EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C350-ORG & REPOSITORY INDENTIFICATI.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C347-WILL''S WORLD JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C344-CLOUD EVALUATION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C349-EZID UK.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C348-DIGGING INTO DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C352-KB+.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C351-REPOSITORY JUNCTION BROKER.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C345-AUTHORISATION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C353 - SERVICE ENHANCEMENT.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C355 - DIGIMAP 13-14.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C357 - LOCKSS SERVICE.xlsx]Summary'!$C:$J,3,FALSE),0)
There must be an easier way to pull all this information in rather than this very long formula. Which I have discovered can become corrupted when I copy the file across to a new financial year and the files they are pointing to are not refreshed or updated. I would like to put something in place that would be very robust so I don't have to fix things each month. This formula is likely to grow depending on how many new codes/workbooks are created in any given month. I hope you can suggest a better working practice. Is a macro the way to go, or something with PowerPivot?
Best regards,
Peggy
=IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C202-ESPA DATA EVIDENCE & PARTNERSH.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C100-DIGIMAP NATIONAL SERVICES.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C201-DIGIMAP STREAM SCHOOLS OS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C101-JORUM+.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C104-ACCESS MANAGEMENT.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C314-LOCKSS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C322-ABLE EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C000-JISC CORE FUNDING.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C330-GECO - JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C317-PECAN EJOURNAL ARCHIVE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C329-LINKED DATA - JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C319-JUNCTION OPEN ACCESS REPOS JIS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C328-MANTRA DATA MGMT - EDINA-JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C102-SDSS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C309-PEPRS EDINA.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C321-GEO SOILS E-FRAMEWORK.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C331-DIGITAL MONOGRAPH.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C103-MEDIA HUB JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C334-SHARED OPENURL DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C333-IGBIOS GEOSPATIAL JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C341-MEDIA HUB ENGAGEMENT-EASE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C342-MOBILE EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C338-USED - USABILITY ENHANCE JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C335-GOLD- GOGEO LINKED DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C343-HARDWARE REPLACEMENT JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C339-SONEX EXTENSION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C336-SUNCAT OPEN METADATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP(C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C337-SHIBBOLETH CONSORTIUM.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C332-STEEV GEOSPATIAL JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C358 - SAFENET.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C340-REPOSITORY NET JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C356 - KEEPERS.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C346-DISCOVER EDINA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C350-ORG & REPOSITORY INDENTIFICATI.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C347-WILL''S WORLD JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C344-CLOUD EVALUATION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C349-EZID UK.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C348-DIGGING INTO DATA JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C352-KB+.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C351-REPOSITORY JUNCTION BROKER.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C345-AUTHORISATION JISC.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C353 - SERVICE ENHANCEMENT.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C355 - DIGIMAP 13-14.xlsx]Summary'!$C:$J,3,FALSE),0)+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C357 - LOCKSS SERVICE.xlsx]Summary'!$C:$J,3,FALSE),0)
There must be an easier way to pull all this information in rather than this very long formula. Which I have discovered can become corrupted when I copy the file across to a new financial year and the files they are pointing to are not refreshed or updated. I would like to put something in place that would be very robust so I don't have to fix things each month. This formula is likely to grow depending on how many new codes/workbooks are created in any given month. I hope you can suggest a better working practice. Is a macro the way to go, or something with PowerPivot?
Best regards,
Peggy