Shorten formula

[nandomer]

Kind of new to excel - can someone help by shortening this formula:

=[Invoices.xls]PR1!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR2'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR3'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR4'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR5'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR6'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR7'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR8'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR9'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR10'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR11'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR12'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR13'!$L$51+'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR14'!$L$51

Thanks in advance!

[p24leclerc]

I don't think it is at all possible. Your formula refers to cells in other workbooks and it have to specify the full path to find the files it refers to. one way to reduce this formula is to change the path to find the files. Less sub-directories; shorter names; etc.
Another possibility would be to condense all those files in a big one with multiple sheets.
Good luck

[kvsrinivasamurthy]

If the file H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]

is in the same directory replace

H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]

by

[Invoices.xls]

Otherwise in this file H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]
open new sheet PR15. In that Sheet PR15 put the formula in L51 cell

=PR1!$L$51+PR2!$L$51+.......+PR14!$L$51

The present formula replace with
'H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR15'!$L$51

[snb]

use named ranges; e.g.

PR1:
='H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR1'!$L$51

PR2:
='H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR2'!$L$51

PR3:
='H:\Year 2011\NPM CONST\Blank\Pay Requests\[Invoices.xls]PR3'!$L$51


In the formula you can use

=PR1+PR2+PR3 

Or you can change the workbookproperty 'hyperlinkbase' into H:\Year 2011\NPM CONST\Blank\Pay Requests\

now in the formula you only have to enter

[Invoices.xls]PR3'!$L$51 +[Invoices.xls]PR4'!$L$51