Help with a formula please.

In column "A" I have about 400 file names with each path, for example:

D:\Folio_02\AL605_02.xls

Each file contains a value named "ATT" and another named "XBB". I need to
get these values in adjoining columns. I've tried every variation of the
following I can think of without success:

A1&"!"&ATT

None have worked. I'm sure I have done this before but can't remember and
can't find any of my old files with an example.

Please help.

Jim

More like: A1&"!ATT"
************
Anne Troy
www.OfficeArticles.com


"jim simpson" <[email protected]> wrote in message
news:vAKNe.15030$Co1.6328@lakeread01...
> In column "A" I have about 400 file names with each path, for example:
>
> D:\Folio_02\AL605_02.xls
>
> Each file contains a value named "ATT" and another named "XBB". I need to
> get these values in adjoining columns. I've tried every variation of the
> following I can think of without success:
>
> A1&"!"&ATT
>
> None have worked. I'm sure I have done this before but can't remember and
> can't find any of my old files with an example.
>
> Please help.
>
> Jim
>
>

Thanks Anne,

That looks like what I need but it gives a string like

D:\Folio_02\AL605_02.xls!ATT

which is not a folmula. I think I need something like this

='D:\Folio_02\AL605_02.xls'!ATT

How can I get this without opening the file and going through the
PasteSpecial routine in order to link the cells?

Can you give me a little more help?

Thanks

Jim


"Anne Troy" <[email protected]> wrote in message
news:%[email protected]...
> More like: A1&"!ATT"
> ************
> Anne Troy
> www.OfficeArticles.com
>
>
> "jim simpson" <[email protected]> wrote in message
> news:vAKNe.15030$Co1.6328@lakeread01...
> > In column "A" I have about 400 file names with each path, for example:
> >
> > D:\Folio_02\AL605_02.xls
> >
> > Each file contains a value named "ATT" and another named "XBB". I need
to
> > get these values in adjoining columns. I've tried every variation of the
> > following I can think of without success:
> >
> > A1&"!"&ATT
> >
> > None have worked. I'm sure I have done this before but can't remember
and
> > can't find any of my old files with an example.
> >
> > Please help.
> >
> > Jim
> >
> >
>
>

And the single quotes

="'"&A1&"'!ATT"

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Anne Troy" <[email protected]> wrote in message
news:%[email protected]...
> More like: A1&"!ATT"
> ************
> Anne Troy
> www.OfficeArticles.com
>
>
> "jim simpson" <[email protected]> wrote in message
> news:vAKNe.15030$Co1.6328@lakeread01...
> > In column "A" I have about 400 file names with each path, for example:
> >
> > D:\Folio_02\AL605_02.xls
> >
> > Each file contains a value named "ATT" and another named "XBB". I need
to
> > get these values in adjoining columns. I've tried every variation of the
> > following I can think of without success:
> >
> > A1&"!"&ATT
> >
> > None have worked. I'm sure I have done this before but can't remember
and
> > can't find any of my old files with an example.
> >
> > Please help.
> >
> > Jim
> >
> >
>
>

In cell B2, enter: ="$$'"&A2&"'!ATT"

In cell C2, enter: ="$$'"&A2&"'!XBB"

Copy the formulas down to the last row of file names

Select columns B and C, and copy them
Choose Edit>Paste Special
Select Values, click OK

With Columns B and C still selected, choose Edit>Replace
In the Find What box, type: $$
In the Replace With box, type: =
Click the Replace All button

jim simpson wrote:
> Thanks Anne,
>
> That looks like what I need but it gives a string like
>
> D:\Folio_02\AL605_02.xls!ATT
>
> which is not a folmula. I think I need something like this
>
> ='D:\Folio_02\AL605_02.xls'!ATT
>
> How can I get this without opening the file and going through the
> PasteSpecial routine in order to link the cells?
>
> Can you give me a little more help?
>
> Thanks
>
> Jim
>
>
> "Anne Troy" <[email protected]> wrote in message
> news:%[email protected]...
>
>>More like: A1&"!ATT"
>>************
>>Anne Troy
>>www.OfficeArticles.com
>>
>>
>>"jim simpson" <[email protected]> wrote in message
>>news:vAKNe.15030$Co1.6328@lakeread01...
>>
>>>In column "A" I have about 400 file names with each path, for example:
>>>
>>>D:\Folio_02\AL605_02.xls
>>>
>>>Each file contains a value named "ATT" and another named "XBB". I need
>>
> to
>
>>>get these values in adjoining columns. I've tried every variation of the
>>>following I can think of without success:
>>>
>>>A1&"!"&ATT
>>>
>>>None have worked. I'm sure I have done this before but can't remember
>>
> and
>
>>>can't find any of my old files with an example.
>>>
>>>Please help.
>>>
>>>Jim
>>>
>>>
>>
>>
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks to al who responded. I now have it working.

Jim


"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> In cell B2, enter: ="$$'"&A2&"'!ATT"
>
> In cell C2, enter: ="$$'"&A2&"'!XBB"
>
> Copy the formulas down to the last row of file names
>
> Select columns B and C, and copy them
> Choose Edit>Paste Special
> Select Values, click OK
>
> With Columns B and C still selected, choose Edit>Replace
> In the Find What box, type: $$
> In the Replace With box, type: =
> Click the Replace All button
>
> jim simpson wrote:
> > Thanks Anne,
> >
> > That looks like what I need but it gives a string like
> >
> > D:\Folio_02\AL605_02.xls!ATT
> >
> > which is not a folmula. I think I need something like this
> >
> > ='D:\Folio_02\AL605_02.xls'!ATT
> >
> > How can I get this without opening the file and going through the
> > PasteSpecial routine in order to link the cells?
> >
> > Can you give me a little more help?
> >
> > Thanks
> >
> > Jim
> >
> >
> > "Anne Troy" <[email protected]> wrote in message
> > news:%[email protected]...
> >
> >>More like: A1&"!ATT"
> >>************
> >>Anne Troy
> >>www.OfficeArticles.com
> >>
> >>
> >>"jim simpson" <[email protected]> wrote in message
> >>news:vAKNe.15030$Co1.6328@lakeread01...
> >>
> >>>In column "A" I have about 400 file names with each path, for example:
> >>>
> >>>D:\Folio_02\AL605_02.xls
> >>>
> >>>Each file contains a value named "ATT" and another named "XBB". I need
> >>
> > to
> >
> >>>get these values in adjoining columns. I've tried every variation of
the
> >>>following I can think of without success:
> >>>
> >>>A1&"!"&ATT
> >>>
> >>>None have worked. I'm sure I have done this before but can't remember
> >>
> > and
> >
> >>>can't find any of my old files with an example.
> >>>
> >>>Please help.
> >>>
> >>>Jim
> >>>
> >>>
> >>
> >>
> >
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>