File name as variable in a cell call?
Kilauhea
-
Kilauhea -
Kilauhea -
Hello,
I am looking to call values and text strings from several quite similar files.
(these are invoices, I would like to retrieve the references, amounts excluding tax and VAT, etc. to create a report...).
I know how to point to cells one by one by opening the files, but it's long and tedious.
I was wondering if there was a way to set a filename as a variable.
Ideal example:
I paste the filename into a cell L8: "myfiletoimport.xls'
and to retrieve my values I type...
= (L8)!totalHT (for the cell "totalHT)
= (L8)!reference (for the reference) etc.
except that of course the parentheses are not correct syntax
I imagine there is a function for that?... and I can't find it :(
Anyway, if you know the syntax and/or have a method to retrieve repetitive data from a list of files, I would be grateful.
Thank you for your attention
François
Configuration: Mac OS X (10.9.2) / Chrome 33.0.1750.152
I am looking to call values and text strings from several quite similar files.
(these are invoices, I would like to retrieve the references, amounts excluding tax and VAT, etc. to create a report...).
I know how to point to cells one by one by opening the files, but it's long and tedious.
I was wondering if there was a way to set a filename as a variable.
Ideal example:
I paste the filename into a cell L8: "myfiletoimport.xls'
and to retrieve my values I type...
= (L8)!totalHT (for the cell "totalHT)
= (L8)!reference (for the reference) etc.
except that of course the parentheses are not correct syntax
I imagine there is a function for that?... and I can't find it :(
Anyway, if you know the syntax and/or have a method to retrieve repetitive data from a list of files, I would be grateful.
Thank you for your attention
François
Configuration: Mac OS X (10.9.2) / Chrome 33.0.1750.152
1 answer
-
Hello
If in a cell A2 you have the complete address: 'myfile'!reference, you can reference it in another cell with INDIRECT(A2)
or if you want to reconstruct the address:
in A2 the name without the ': myfile
in the other cell: = INDIRECT("'" & A2 & "'!reference")
Best regards
"Imagination is more important than knowledge." A. Einstein-
The address is partly derived from a file name (in a variable) and partly entered manually...
The ideal scenario would be to retrieve data with the same name from multiple Excel files.
For example:
'file1.xls'!reference
'file1.xls'!montantHT
'file1.xls'!montantTTC
then...
'file2.xls'!reference
'file2.xls'!montantHT
'file2.xls'!montantTTC
etc.
So I was thinking that putting the file name in a cell (here L8 containing 'file1.xls')
would allow for repetitive calls... L8!reference then L8!montantHT etc. -
Yes, that's what I added in my first post, you can recompose the address
in L8 you put fichier1.xls but without the ' (we put them in the INDIRECT formula otherwise it won't work)
In the other cell you put INDIRECT("'" & L8 & "'!reference") a first ' between the first two quotation marks, the other ' before the !
Best regards
-
