getting data from a set of closed files

  • Thread starter Thread starter rstolk
  • Start date Start date
R

rstolk

here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin
(abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My
Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1


some more specifics:

I do not want any hardcoded file names is the new xls file
or to type in the full path etc.etc
nor are any of these files open.
please note that all the files including the new to fill file all
reside in the same directory.

to specify a little more:

I have a working directory containging multiple xls file
numberd e.g. 1.xls 2.xls 3.xls and so on

so when I type 1 in A1 of the new xls file I want the formula
in B1 to pick up the data of A1 from the file 1.xls
if I were to type 2 it would pick up A1 from the file 2.xls
and so on......

hope someone knows how to do this

thanks in advance

regards

Richard
 
The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]
here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin
(abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My
Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1

some more specifics:

I do not want any hardcoded file names is the new xls file
or to type in the full path etc.etc
nor are any of these files open.
please note that all the files including the new to fill file all
reside in the same directory.

to specify a little more:

I have a working directory containging multiple xls file
numberd e.g. 1.xls 2.xls 3.xls and so on

so when I type 1 in A1 of the new xls file I want the formula
in B1 to pick up the data of A1 from the file 1.xls
if I were to type 2 it would pick up A1 from the file 2.xls
and so on......

hope someone knows how to do this

thanks in advance

regards

Richard
Click to expand...
 
Hi Dave

Thanks for your reaction, but I am a newbie and realy dont know how to
work this VB stuff

I really only would like to get it done with a formula,
do you think I am asking the impossible ?

best regards

Richard



Dave Peterson said:
The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]
here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin
(abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My
Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1

some more specifics:

I do not want any hardcoded file names is the new xls file
or to type in the full path etc.etc
nor are any of these files open.
please note that all the files including the new to fill file all
reside in the same directory.

to specify a little more:

I have a working directory containging multiple xls file
numberd e.g. 1.xls 2.xls 3.xls and so on

so when I type 1 in A1 of the new xls file I want the formula
in B1 to pick up the data of A1 from the file 1.xls
if I were to type 2 it would pick up A1 from the file 2.xls
and so on......

hope someone knows how to do this

thanks in advance

regards

Richard
Click to expand...
Click to expand...
 
Dear All,

INDIREXT.EXT did the trick, thanks for all your help and sugestions !

best regards

Richard














Hi Dave

Thanks for your reaction, but I am a newbie and realy dont know how to
work this VB stuff

I really only would like to get it done with a formula,
do you think I am asking the impossible ?

best regards

Richard



Dave Peterson said:
The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

But Harlan Grove posted a function that opens another instance of excel and then
retrieves the value from that closed workbook.

http://google.com/[email protected]
here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin
(abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My
Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1

some more specifics:

I do not want any hardcoded file names is the new xls file
or to type in the full path etc.etc
nor are any of these files open.
please note that all the files including the new to fill file all
reside in the same directory.

to specify a little more:

I have a working directory containging multiple xls file
numberd e.g. 1.xls 2.xls 3.xls and so on

so when I type 1 in A1 of the new xls file I want the formula
in B1 to pick up the data of A1 from the file 1.xls
if I were to type 2 it would pick up A1 from the file 2.xls
and so on......

hope someone knows how to do this

thanks in advance

regards

Richard
Click to expand...
Click to expand...
Click to expand...
 
Back
Top