find a cell in a file you specify in a different file

Hi Richard, and welcome to the board.
The best way to get this is to open up both files. (Say they are named Old File and New File)
In New File, in the cell you want to see the info in, press = and then go to Old File, find the cell with the info you want to see, click on it and press Enter.
You should end up with a reference in your formula bar that looks something like:
='C:\My Documents\Monthly Files\Last Month''s Logs\[Old File.xls]Sheet1'!$D$12

Where the info you want in New File is located in cell D12 of Sheet1 in Old File. (Obviously the folder names will be different, reflecting your actual folder names instead of mine.) The info in New File will be updated if the info in Old File gets changed.

Does this help?

Hello,

maybe you are looking for something like this:
the master sheet that is linked to this sheet is determined by the value in the cell G2. In the source sheet there are named ranges like product1amount, product1margin, etc.

BOTH FILES MUST BE OPENED, OTHERWISE YOU GET REF#!

Martin

Hello HalfAce

Its getting very near, actually I had gotten this far already.
But...
What I need is :
='C:\My Documents\Monthly Files\Last Month''s Logs\[Old File.xls]Sheet1'!$D$12

where "old file.xls" is generated by the value I but in e.g. the cell A1 of the new file

e.g.

In the new file I would type "abc" in cell A1, this sould result in getting data in the cell D12 from the file abc.xls

so what I am actually looking for is a variable filename

hope someone can help

thanks

regards

Richard

Let me try and explain in a little more detail :

here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin (abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1

Hi Richard,
I understand what you're looking for now and am playing around with the idea. (Haven't got it to work yet.) Never seen this done before so I don't know how to do it. Very interesting idea though so maybe someone better than me can help out.

rstolk said:

Let me try and explain in a little more detail :

here is what I am trying to do,

I would like to type data in cel A1 of file xxx.xls
(e.g abc)

I want to use this input to fill cel B1 of file xxx.xls
with the data in a cell (e.g A1) from the file abc.xls

so as part of the formula the filename of the extrnal file to lookin (abc.xls)
must be built from the input of cell A1 of file xxx.xls

I have gotten as far as this:

='C:\Documents and Settings\Richard\My Documents\[test.xls]Sheet1'!$A$1

but the file name here (test.xls) is hardcoded and I need it to be
created by the input form cel A1

Click to expand...

In B1 enter:

C:\Documents and Settings\Richard\My Documents\[test.xls]

Now you can use:

=INDIRECT("'"&B1&"'!A1")

The target book/file must be open for this formula to work.

As shown by Aladin, you must either use INDIRECT with the files open or you must hard-code the filename.

If you wish, you could take referencing the INDIRECT function further by placing the path, filename and worksheet name in worksheet cells e.g.

A1: C:\Documents and Settings\Richard\My Documents\
A2: test.xls
A3: Sheet1
(note the final backslash in the path reference)

Then use the INDIRECT as follows:

=INDIRECT("'"&A1&"["&A2&"]"&A3&"'!"&"A1")

If you wish to hardcode the worksheet name (i.e. skip the cell reference in A3):

=INDIRECT("'"&A1&"["&A2&"]"&"Sheet1'!A1")

For other ideas on how to get a value(s) from a closed workbook, see:

http://www.mrexcel.com/board2/viewtopic.php?t=67095
http://www.mrexcel.com/board2/viewtopic.php?t=67212

In the first reference, Juan Pablo González suggests looking at the INDIRECT.EXT function that is available from the MoreFunc add-in. Unlike INDIRECT, the INDIREXT.EXT it is able to read values from closed workbooks. Get the free add-in from:

http://longre.free.fr/english
(scroll down to MoreFunc)

If you decide on the Indirect.ext function, I suggest that you then look at this site for problems experienced by Mr. Harlan Grove (Microsoft Excel MVP). More importantly, Mr. Laurent Longre, the author of the MoreFunc add-in, answers those problems and gives examples on how to use Indirect.Ext.
http://makeashorterlink.com/?H2AE42BA6

Finally, I am successfully using an UDF from Harlan Grove to extract value(s) from a closed workbook. Refer.
http://makeashorterlink.com/?C1F842336

Warning:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

Good luck,

Mike

Dear all,

Thanks for all your sudgestions, but....
this is still not what I am looking for.

I do not want any hardcoded file names is the new xls file
or to type in the full path etc.etc
nor are any of these files open.
please note that all the files including the new to fill file all reside in the same directory.

to specify a little more:

I have a working directory containging multiple xls file
numberd e.g. 1.xls 2.xls 3.xls and so on

so when I type 1 in A1 of the new xls file I want the formula
in B1 to pick up the data of A1 from the file 1.xls
if I were to type 2 it would pick up A1 from the file 2.xls
and so on......

still hoping some one can find a way to do this

thanks

regards

Richard

Variable links I use them all the time in VBA and Excel. I'm not sure how to do it in an Excel formula however, its quite easy to accomplish with a VB script using the .Changelink function.

Basically you would do the initial link with the formula like Halface suggested then whenever you want to change the link upon changing a text value in a cell you can use an event to pull your new file link name that you type into a target cell and use that value to change the link in VB script.
What this does is in turn automatically changes the linked formula point to your new link.

Do a search for the changelink function in the help file for examples.