From: marc747 on
Hi,
I have 19 excel files in a folder and I have created another excel
file (B) in which I am trying to set a formula that can look in Cell
"A6" which has the path with the file name of the first file (1) then
copy From the first File (1) cell "L25" an place it in Cell "Y6" of
File (B).

I want the file names or file names with the path to be in file (B) so
that I can change the name easily instead of changing all formulas.

Hope I have clear explanation!

Thanks,

From: Dave Peterson on
The function you'd want to use is =indirect().

But =indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

marc747 wrote:
>
> Hi,
> I have 19 excel files in a folder and I have created another excel
> file (B) in which I am trying to set a formula that can look in Cell
> "A6" which has the path with the file name of the first file (1) then
> copy From the first File (1) cell "L25" an place it in Cell "Y6" of
> File (B).
>
> I want the file names or file names with the path to be in file (B) so
> that I can change the name easily instead of changing all formulas.
>
> Hope I have clear explanation!
>
> Thanks,

--

Dave Peterson
From: marc747 on
Hi,
How would I make the Formula grab the path or file name from the Cell
that I have the file names listed.

Thanks,



On Apr 12, 3:26 pm, Dave Peterson <peter...(a)verizonXSPAM.net> wrote:
> The function you'd want to use is =indirect().
>
> But =indirect() won't work if the sending file is closed.
>
> Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
> orhttp://xcell05.free.fr/morefunc/english/index.htm
>
> That includes =indirect.ext() that may help you.
>
> ===
> If you have trouble getting to the site, then search google for indirect.ext.
>
> I found this alternative site:http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
>
> I didn't look to see if it was the most current version.
>
> I'd check the original site every so often to see if it's working.
>
> marc747 wrote:
>
> > Hi,
> > I have 19 excel files in a folder and I have created another excel
> > file (B) in which I am trying to set a formula that can look in Cell
> > "A6" which has the path with the file name of the first file (1) then
> > copy From the first File (1) cell "L25" an place it in Cell "Y6" of
> > File (B).
>
> > I want the file names or file names with the path to be in file (B) so
> > that I can change the name easily instead of changing all formulas.
>
> > Hope I have clear explanation!
>
> > Thanks,
>
> --
>
> Dave Peterson

From: Dave Peterson on
The string you'd want to use without the =indirect.ext() would look like:
='C:\My Documents\excel\[book 1.xls]Sheet1'!A1

So depending on what you have in those cells:
=indirect.ext("'"&a1&"["&a2&"]"&A3&"'!"&a4)

Where
A1 contains the path (with a final backslash)
A2 contains the filename
A3 contains the sheetname
A4 contains the cell address



marc747 wrote:
>
> Hi,
> How would I make the Formula grab the path or file name from the Cell
> that I have the file names listed.
>
> Thanks,
>
> On Apr 12, 3:26 pm, Dave Peterson <peter...(a)verizonXSPAM.net> wrote:
> > The function you'd want to use is =indirect().
> >
> > But =indirect() won't work if the sending file is closed.
> >
> > Laurent Longre has an addin (morefunc.xll) at:http://xcell05.free.fr/
> > orhttp://xcell05.free.fr/morefunc/english/index.htm
> >
> > That includes =indirect.ext() that may help you.
> >
> > ===
> > If you have trouble getting to the site, then search google for indirect.ext.
> >
> > I found this alternative site:http://download.cnet.com/Morefunc/3000-2077_4-10423159.html
> >
> > I didn't look to see if it was the most current version.
> >
> > I'd check the original site every so often to see if it's working.
> >
> > marc747 wrote:
> >
> > > Hi,
> > > I have 19 excel files in a folder and I have created another excel
> > > file (B) in which I am trying to set a formula that can look in Cell
> > > "A6" which has the path with the file name of the first file (1) then
> > > copy From the first File (1) cell "L25" an place it in Cell "Y6" of
> > > File (B).
> >
> > > I want the file names or file names with the path to be in file (B) so
> > > that I can change the name easily instead of changing all formulas.
> >
> > > Hope I have clear explanation!
> >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson