Fill series in formula

[LarsP1]

Hello,

I'm trying to create a simple client database in excel. It gathers its data automatically from the invoices (also excel files).
excel.png
As you can see in the formula line, the data comes from another excel file.
Now I want to use the fill handle and pull the formula down, so that the invoice no. (Factuurnummer) corresponds to the number on the left, like it is in A3 and B3. So that B4 will be: ='C:\Users\Garage\Dropbox\ARA\[Factuur2015110002.xlsx]Blad1'!$C$7
I've tried a few things already, inserting [Factuurnummer " & A3 & ".xlsx] seems not to be working. Of course i could do this all manually but I think there is a way to use the fill handle. Anyone has an idea?

Thanks in advance!

[sandy666]

an idea:
list of workbooks (or paths), e.g.
Factuur2015110001.xlsx
Factuur2015110002.xlsx
Factuur2015110003.xlsx
and INDIRECT()

[LarsP1]

Yes I know INDIRECT, but that only works if all the workbooks are opened.

[sandy666]

maybe it will help
Replace INDIRECT with INDEX to iterate closed workbooks

[LarsP1]

I couldn't make it work with INDEX/INDIRECT

However i've managed to create this:
excel3.png
So now it just returns the correct path, but i can't make the reference work. Whenever I remove the first =", I get a reference error.

[sandy666]

picture is not clickable so......

[LarsP1]

I'm not sure what you mean/want?

[sandy666]

the best way is upload example workbook (.xlsx file) without any top secret data

try adapt this example:
INDEX('path[name_of_file]name_of_sheet'!An:En,SMALL(IF('path[name_of_file]name_of_sheet'!An:An&'path[name_of_file]name_of_sheet'!$Dn:Dn=An&Dn,ROW('path[name_of_file]name_of_sheet'!An:An)),ROW()-1),5)

[LarsP1]

clientdatabase.xlsx
I've uploaded the workbook where i made the autofill work, but now it is just showing the path where the file is located, not the value i refer to.

[sandy666]

I have no "golden advice". Example index from above is working for me (with CSE) with opened or closed workbooks.

[oeldere]

a3 in your file is ="C:\Users\Garage\Dropbox\ARA\[Factuur"&A3&".xlsx]Blad1'!$C$7"

And what if you change it in =C:\Users\Garage\Dropbox\ARA\[Factuur"&A3&".xlsx]Blad1'!$C$7

Remove the " at the begin and end of the cell.

[LarsP1]

Than it says that the formula contains an error.

[oeldere]

which error?

[LarsP1]

It doesn't say, only that it contains an error and i can visit the help section to see frequent errors.