Filename from a Cell

baridude · 07-25-2006, 11:00 AM

I'm trying to get some values from particular worksheets in other excel files
into my currently open file. I'm able to build up the filenames and put them
in a cell, say B2.

Now, I'd like to use the filename in cell B2 to get the values from that file.

Let's say B2 now contains filename_20060712.xls.

In another cell I have,

='U:\foo\[filename_20060712.xls]Sheet3'!$I$15

What I'd like to do is use the contents of cell B2 as the variable inside
the [] to get the filename as a variable. Once I get that working, I can
just stick the function I have in B2 to build the filename into the []. It
seems whatever I stick in the brackets is treated as a filename and excel
tries to open it, even if it is something like [$B2].

I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
if I can. I don't have it in me to learn "yet another language."

The online docs have been no help and I haven't seen any simular post here
in this newsgroup.

Marcelo · 07-25-2006, 11:25 AM

Hi,

I am not sure if I understand exactly what you are looking for, but indirect
and address functions embeded could help.

so =indirect(address(2,2,1,1,b2)
when
2 = row number
2 = column number (B is the 2nd)
1 = Absolute or relative colud be 1,2,3 or 4
1 = a1 or r1c1
b2 = [filename_20060712.xls]Sheet3

Note that the workbook, filename_20060712 must be open, to it run

hth
regards from Brazil
Marcelo

"baridude" escreveu:

> I'm trying to get some values from particular worksheets in other excel files
> into my currently open file. I'm able to build up the filenames and put them
> in a cell, say B2.
>
> Now, I'd like to use the filename in cell B2 to get the values from that file.
>
> Let's say B2 now contains filename_20060712.xls.
>
> In another cell I have,
>
> ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
>
> What I'd like to do is use the contents of cell B2 as the variable inside
> the [] to get the filename as a variable. Once I get that working, I can
> just stick the function I have in B2 to build the filename into the []. It
> seems whatever I stick in the brackets is treated as a filename and excel
> tries to open it, even if it is something like [$B2].
>
> I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> if I can. I don't have it in me to learn "yet another language."
>
> The online docs have been no help and I haven't seen any simular post here
> in this newsgroup.
>
>

David Billigmeier · 07-25-2006, 11:50 AM

Try:

=INDIRECT('U:\foo\[filename_20060712.xls]Sheet3'!$I$15)

--
Regards,
Dave

"baridude" wrote:

> I'm trying to get some values from particular worksheets in other excel files
> into my currently open file. I'm able to build up the filenames and put them
> in a cell, say B2.
>
> Now, I'd like to use the filename in cell B2 to get the values from that file.
>
> Let's say B2 now contains filename_20060712.xls.
>
> In another cell I have,
>
> ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
>
> What I'd like to do is use the contents of cell B2 as the variable inside
> the [] to get the filename as a variable. Once I get that working, I can
> just stick the function I have in B2 to build the filename into the []. It
> seems whatever I stick in the brackets is treated as a filename and excel
> tries to open it, even if it is something like [$B2].
>
> I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> if I can. I don't have it in me to learn "yet another language."
>
> The online docs have been no help and I haven't seen any simular post here
> in this newsgroup.
>
>

baridude · 07-25-2006, 02:55 PM

Perhaps I wasn't clear enough in my question.

If I put:

='U:\foo\[filename_20060712.xls]Sheet3'!$I$15

in a cell in my worksheet, I get the value that I want from the file
filename_20060712.xls on Sheet3 from cell I15. This all works fine and dandy.

What I want is the string inside the [] to be a variable. For example, if I
have in cell B2, filename_20060712.xls, then I would like this to work:

='U:\foo\[$B2]Sheet3'!$I$15

but it does not work, excel tries to open the file $B2.

Even better the contents of my brackets would look like this:

CONCATENATE(CONCATENATE("filename_",YEAR(A2),TEXT(MONTH(A2),"00"),TEXT(DAY(A2),"00")".xls"))

Which is how I build the filename that I put in B2.

My whole goal is that I have a directory full of dated files and I want to
get certain values from each file into a single spreadsheet. I can type in
the name for each file, but that seems silly since that's what programs and
computers are for.

"David Billigmeier" wrote:

> Try:
>
> =INDIRECT('U:\foo\[filename_20060712.xls]Sheet3'!$I$15)
>
>
> --
> Regards,
> Dave
>
>
> "baridude" wrote:
>
> > I'm trying to get some values from particular worksheets in other excel files
> > into my currently open file. I'm able to build up the filenames and put them
> > in a cell, say B2.
> >
> > Now, I'd like to use the filename in cell B2 to get the values from that file.
> >
> > Let's say B2 now contains filename_20060712.xls.
> >
> > In another cell I have,
> >
> > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> >
> > What I'd like to do is use the contents of cell B2 as the variable inside
> > the [] to get the filename as a variable. Once I get that working, I can
> > just stick the function I have in B2 to build the filename into the []. It
> > seems whatever I stick in the brackets is treated as a filename and excel
> > tries to open it, even if it is something like [$B2].
> >
> > I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> > if I can. I don't have it in me to learn "yet another language."
> >
> > The online docs have been no help and I haven't seen any simular post here
> > in this newsgroup.
> >
> >

JBoulton · 07-25-2006, 06:35 PM

Put the file name and cell address in A1 as text. Use a text formula to
create the full path in A2. Or just put the full text including the path in
A2. Start with a ' not an =.
Then =indirect(a2) will give you what you want. Change the file name in A1
and the result will change.
--
Jim

"baridude" wrote:

> Perhaps I wasn't clear enough in my question.
>
> If I put:
>
> ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
>
> in a cell in my worksheet, I get the value that I want from the file
> filename_20060712.xls on Sheet3 from cell I15. This all works fine and dandy.
>
> What I want is the string inside the [] to be a variable. For example, if I
> have in cell B2, filename_20060712.xls, then I would like this to work:
>
> ='U:\foo\[$B2]Sheet3'!$I$15
>
> but it does not work, excel tries to open the file $B2.
>
> Even better the contents of my brackets would look like this:
>
> CONCATENATE(CONCATENATE("filename_",YEAR(A2),TEXT(MONTH(A2),"00"),TEXT(DAY(A2),"00")".xls"))
>
> Which is how I build the filename that I put in B2.
>
> My whole goal is that I have a directory full of dated files and I want to
> get certain values from each file into a single spreadsheet. I can type in
> the name for each file, but that seems silly since that's what programs and
> computers are for.

>
>
> "David Billigmeier" wrote:
>
> > Try:
> >
> > =INDIRECT('U:\foo\[filename_20060712.xls]Sheet3'!$I$15)
> >
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "baridude" wrote:
> >
> > > I'm trying to get some values from particular worksheets in other excel files
> > > into my currently open file. I'm able to build up the filenames and put them
> > > in a cell, say B2.
> > >
> > > Now, I'd like to use the filename in cell B2 to get the values from that file.
> > >
> > > Let's say B2 now contains filename_20060712.xls.
> > >
> > > In another cell I have,
> > >
> > > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> > >
> > > What I'd like to do is use the contents of cell B2 as the variable inside
> > > the [] to get the filename as a variable. Once I get that working, I can
> > > just stick the function I have in B2 to build the filename into the []. It
> > > seems whatever I stick in the brackets is treated as a filename and excel
> > > tries to open it, even if it is something like [$B2].
> > >
> > > I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> > > if I can. I don't have it in me to learn "yet another language."
> > >
> > > The online docs have been no help and I haven't seen any simular post here
> > > in this newsgroup.
> > >
> > >

baridude · 07-27-2006, 06:50 PM

Thanks guys, but none of these do it.

indirect (even if it did work) is not workable since it requires that the
hundreds of other files to be open.

The whole trick it to get the value inside of the [] to be a variable and
recognized as such.

Are there other places to get Excel help?

Other than that, I'll guess I'll have to go learn VBA.

"JBoulton" wrote:

> Put the file name and cell address in A1 as text. Use a text formula to
> create the full path in A2. Or just put the full text including the path in
> A2. Start with a ' not an =.
> Then =indirect(a2) will give you what you want. Change the file name in A1
> and the result will change.
> --
> Jim
>
>
> "baridude" wrote:
>
> > Perhaps I wasn't clear enough in my question.
> >
> > If I put:
> >
> > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> >
> > in a cell in my worksheet, I get the value that I want from the file
> > filename_20060712.xls on Sheet3 from cell I15. This all works fine and dandy.
> >
> > What I want is the string inside the [] to be a variable. For example, if I
> > have in cell B2, filename_20060712.xls, then I would like this to work:
> >
> > ='U:\foo\[$B2]Sheet3'!$I$15
> >
> > but it does not work, excel tries to open the file $B2.
> >
> > Even better the contents of my brackets would look like this:
> >
> > CONCATENATE(CONCATENATE("filename_",YEAR(A2),TEXT(MONTH(A2),"00"),TEXT(DAY(A2),"00")".xls"))
> >
> > Which is how I build the filename that I put in B2.
> >
> > My whole goal is that I have a directory full of dated files and I want to
> > get certain values from each file into a single spreadsheet. I can type in
> > the name for each file, but that seems silly since that's what programs and
> > computers are for.

> >
> >
> > "David Billigmeier" wrote:
> >
> > > Try:
> > >
> > > =INDIRECT('U:\foo\[filename_20060712.xls]Sheet3'!$I$15)
> > >
> > >
> > > --
> > > Regards,
> > > Dave
> > >
> > >
> > > "baridude" wrote:
> > >
> > > > I'm trying to get some values from particular worksheets in other excel files
> > > > into my currently open file. I'm able to build up the filenames and put them
> > > > in a cell, say B2.
> > > >
> > > > Now, I'd like to use the filename in cell B2 to get the values from that file.
> > > >
> > > > Let's say B2 now contains filename_20060712.xls.
> > > >
> > > > In another cell I have,
> > > >
> > > > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> > > >
> > > > What I'd like to do is use the contents of cell B2 as the variable inside
> > > > the [] to get the filename as a variable. Once I get that working, I can
> > > > just stick the function I have in B2 to build the filename into the []. It
> > > > seems whatever I stick in the brackets is treated as a filename and excel
> > > > tries to open it, even if it is something like [$B2].
> > > >
> > > > I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> > > > if I can. I don't have it in me to learn "yet another language."
> > > >
> > > > The online docs have been no help and I haven't seen any simular post here
> > > > in this newsgroup.
> > > >
> > > >

HughT · 08-15-2006, 02:55 AM

Hi,

I have just posted a similar question on this very same issue - was hoping
to get some positive response. However the advice is the same use "Indirect"
and as you quite rightly say this requires the workbook to be open in the
first place - and it is opening the workbooks in the first place that is
being requested.

I just wondered if you had any luck with resolving this?

"baridude" wrote:

> Thanks guys, but none of these do it.
>
> indirect (even if it did work) is not workable since it requires that the
> hundreds of other files to be open.
>
> The whole trick it to get the value inside of the [] to be a variable and
> recognized as such.
>
> Are there other places to get Excel help?
>
> Other than that, I'll guess I'll have to go learn VBA.

>
> "JBoulton" wrote:
>
> > Put the file name and cell address in A1 as text. Use a text formula to
> > create the full path in A2. Or just put the full text including the path in
> > A2. Start with a ' not an =.
> > Then =indirect(a2) will give you what you want. Change the file name in A1
> > and the result will change.
> > --
> > Jim
> >
> >
> > "baridude" wrote:
> >
> > > Perhaps I wasn't clear enough in my question.
> > >
> > > If I put:
> > >
> > > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> > >
> > > in a cell in my worksheet, I get the value that I want from the file
> > > filename_20060712.xls on Sheet3 from cell I15. This all works fine and dandy.
> > >
> > > What I want is the string inside the [] to be a variable. For example, if I
> > > have in cell B2, filename_20060712.xls, then I would like this to work:
> > >
> > > ='U:\foo\[$B2]Sheet3'!$I$15
> > >
> > > but it does not work, excel tries to open the file $B2.
> > >
> > > Even better the contents of my brackets would look like this:
> > >
> > > CONCATENATE(CONCATENATE("filename_",YEAR(A2),TEXT(MONTH(A2),"00"),TEXT(DAY(A2),"00")".xls"))
> > >
> > > Which is how I build the filename that I put in B2.
> > >
> > > My whole goal is that I have a directory full of dated files and I want to
> > > get certain values from each file into a single spreadsheet. I can type in
> > > the name for each file, but that seems silly since that's what programs and
> > > computers are for.

> > >
> > >
> > > "David Billigmeier" wrote:
> > >
> > > > Try:
> > > >
> > > > =INDIRECT('U:\foo\[filename_20060712.xls]Sheet3'!$I$15)
> > > >
> > > >
> > > > --
> > > > Regards,
> > > > Dave
> > > >
> > > >
> > > > "baridude" wrote:
> > > >
> > > > > I'm trying to get some values from particular worksheets in other excel files
> > > > > into my currently open file. I'm able to build up the filenames and put them
> > > > > in a cell, say B2.
> > > > >
> > > > > Now, I'd like to use the filename in cell B2 to get the values from that file.
> > > > >
> > > > > Let's say B2 now contains filename_20060712.xls.
> > > > >
> > > > > In another cell I have,
> > > > >
> > > > > ='U:\foo\[filename_20060712.xls]Sheet3'!$I$15
> > > > >
> > > > > What I'd like to do is use the contents of cell B2 as the variable inside
> > > > > the [] to get the filename as a variable. Once I get that working, I can
> > > > > just stick the function I have in B2 to build the filename into the []. It
> > > > > seems whatever I stick in the brackets is treated as a filename and excel
> > > > > tries to open it, even if it is something like [$B2].
> > > > >
> > > > > I'm sure there's some wizbang VBA way to do this, but I'd like to avoid VBA
> > > > > if I can. I don't have it in me to learn "yet another language."
> > > > >
> > > > > The online docs have been no help and I haven't seen any simular post here
> > > > > in this newsgroup.
> > > > >
> > > > >