IF ISERROR - Shortening Formulae

[missnhanley]

Hi Guys,

Hoping this is a quick one you can help me with.

I have a master team spreadsheet that links to many other user spreadsheets.
I am using the ISERROR function to ensure that cells stay blank if there is an error or no user date, my forumla is as follows:

=IF(ISERROR(('S:\FILE PATH\FOLDER\[User 1 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 2 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 3 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 4 Spreadsheet.xls]Daily Summary'!C5)),"",(('S:\FILE PATH\FOLDER\[User 1 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 2 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 3 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 4 Spreadsheet.xls]Daily Summary'!C5))

I now need to add more user spreadsheets and the forumla is too long/big, can anyone suggest a way of shortening this?

Many thanks in advance.

 

[WinteE]

Just a suggestion :

Link the data in a separate worksheet and check for errors on that worksheet.

Success
Erik

 

[missnhanley]

Basically, because there can be no data in the user spreadsheet fields, I am receiving a #VALUE message if I do not use the IF ISERROR function.

Hoping there is a simpler way of doing this without having to have other worksheets in my workbook but thank you for the suggestion.

 

[BillXL]

If you are using Excel 2007, you should be able to use IFERROR, i.e.

=IFERROR(('S:\FILE PATH\FOLDER\[User 1 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 2 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 3 Spreadsheet.xls]Daily Summary'!C5)+('S:\FILE PATH\FOLDER\[User 4 Spreadsheet.xls]Daily Summary'!C5)),"")

Still lengthy, but half the size.

 

[missnhanley]

If you are using Excel 2007, you should be able to use IFERROR

2002 unfortunately, company is behind the times!

 

[missnhanley]

Any other suggestions are greatfully appreciated.