formula linking information from another spreadsheet with another imbeded

Hi there i am having problems finding a fomula for my spreadsheet. i use it to keep track of ivoices i make. each invoice i make is saved as a seperate file.

ive then got another spreadsheet (shown below) to to extract the date, name, and amount from the original invoices.

but

i have to enter the code manually 3 times for each invoice chanceing the name of the file (which is 182.xls on the one below)

but instead of changing the formula name ech time could i use the infomation from colom 'b' as the ref no is also the file name

i thought that this might work but it didnt...
Before
='C:\Documents and Settings\Tom\Desktop\printing invoices\Jobs\[182.xls]Invoice'!$D$45
After
='C:\Documents and Settings\Tom\Desktop\printing invoices\Jobs\[B5]Invoice'!$D$45

please help

click here for a screen shot
\1

Hello and welcome to our board...

Can you please take the time to read our forum rules and then amend your title to be more descriptive of your question.

Thanks.

You have to use the INDIRECT() function, like so:

=INDIRECT("'C:\Documents and Settings\Tom\Desktop\printing invoices\Jobs\["&B5&".xls]Invoice'!$D$45")

but this will only work with if the workbook being referenced is open too.

If you want to work with closed workbooks, you could install the Morefunc.xll addin for free from here: http://xcell05.free.fr/morefunc/english/

and then employ the Indirect.EXT function like so:

=INDIRECT.EXT("'C:\Documents and Settings\Tom\Desktop\printing invoices\Jobs\["&B2&".xls]Invoice'!$D$45")

P.s... thanks for changing your title.