Help with a formula please.

  • Thread starter Thread starter jim simpson
  • Start date Start date
J

jim simpson

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
 
And the single quotes

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Back
Top