How to retrieve data from other filename?

[Guest]

Does anyone have any suggestions on how to retrieve data from other filename?
For example, the filename for my current workbook is Eric - RRRRR.xls, and
the cell A1 under sheet1 for Eric - RRRRR.xls workbook equals to the cell B2
under sheet2 for Temp - RRRRR.xls. And I get a list of workbooks

Eric - R.xls retrieves data from Temp - R.xls
Eric - RR.xks retrieves data from Temp - RR.xls
Eric - RRR.xks retrieves data from Temp - RRR.xls
Eric - RRRR.xks retrieves data from Temp - RRRR.xls
Eric - RRRRR.xks retrieves data from Temp - RRRRR.xls
....

Does anyone have any suggestions on how to code the formula by adding the
number of R in cell A1 under sheet1 for Eric Series workbook? if the current
workbook is Eric - RRRRR.xls, then in cell A1, it will add RRRRR at the end
of the link Temp - for retrieving any data from Temp - RRRRR.xls?
Thanks in advance for any suggestions
Eric

 

[Dave Peterson]

=cell("Filename",a1)
will return the full path/name and sheet name for the cell with the formula in
it.

This formula in A1:
=MID(CELL("filename",A1),1,FIND("]",CELL("filename",A1)))
will return the path/name like:
C:\My Documents\Excel\[Eric - RR.xls]

Then I can use a formula like:
=SUBSTITUTE(SUBSTITUTE(UPPER(A1),"ERIC","Temp"),"R.XLS","RR.XLS")
to get:
C:\MY DOCUMENTS\EXCEL\[Temp - RRR.XLS]

But now the real trouble starts...

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

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

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

I don't use that addin--so good luck!