Shorter/quicker formula for a summary spreadsheet

[Peggy2015]

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

 

[Asala42]

Well it's a brute force sum of 44 vlookups, so not really unless you want to break the formula up into pieces.

There are a couple little things that may help though:
- Use a distinct/reasonably shorter lookup range. eg "Summary'!$C$2:$J$1000" instead of "Summary'!$C:$J". No need to have your vlookups encompass 1 million rows each.
- You can alt enter in formulas - just improves readability
- Every FALSE in your vlookups can be replaced with 0. Again just aids readability and keeps the formula shorter
- You could use named ranges to keep the formula length down as well, though that may be some frontloaded work if you're dealing with 31 unique workbook ranges.

Try this rewrite of your formula:

=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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C101-JORUM+.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C314-LOCKSS.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C322-ABLE EDINA JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C330-GECO - JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C329-LINKED DATA - JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C102-SDSS.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C331-DIGITAL MONOGRAPH.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C103-MEDIA HUB JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C333-IGBIOS GEOSPATIAL JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP(C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C337-SHIBBOLETH CONSORTIUM.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C332-STEEV GEOSPATIAL JISC.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C358 - SAFENET.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C356 - KEEPERS.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C346-DISCOVER EDINA JISC.xlsx]Summary'!$C$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C350-ORG & REPOSITORY INDENTIFICATI.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C344-CLOUD EVALUATION JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2015-16\P5C - EDINA 2015-16\Other Restricted\[J5C352-KB+.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),0)
+IFERROR(VLOOKUP($C8,'U:\ISG\ISCORP\BA_Shared\Financial Forecasting\2014-15\P5C - EDINA\Other Restricted\[J5C345-AUTHORISATION JISC.xlsx]Summary'!$C$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),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$2:$J$2000,3,0),0)

It should be easier to read in your formula bar, where each lookup is it's own line.

 

[Peggy2015]

Hello Asala42,
Thank you very much for your help with this. I certainly looks much cleaner! However, I still have major concerns about how reliable the information is, as typing in these formulas is reliant on humans!

Any thoughts about how pulling this information could become more reliable?

Regards,
Peggy

 

[HoMachine]

Hey Peggy,

Personally, I would break down all the vlookups and iferror statments into 31 different cell, each cell representing each report.
Then, sum them all up @ the end, then just hide the 31 cells. make your sheet look much cleaner and if anything were to go wrong in each cell or sheet, you can identify the issue much quicker and know where it went wrong.

Just my thoughts

Regards,

 

[Peggy2015]

Hello HoMachine,

Thank you for your message and the great idea! I know how to put a macro together that hides the spread sheet so I'll put both to good use! Love the idea of putting these formulas together separately, this will make it far more robust that what's there at the moment!

Thanks for your help.

Best wishes,
Peggy