If the cell will change because someone edits it manually or with code, you
can use the Change Event.

Right click on the sheet tab and select view code. In the left dropdown at
the top of this Sheet module, select Worksheet and in the right dropdown
select CHANGE

this will put in the declaration for the event

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

(selectionchange may also be entered, but you can delete this or ignore it)

You would put code there like this

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$B$9" then
Range("B10").Formula = "=Vlookup(A10,'C:\Myfolder[" & Target.Value & _
"]Sheet3'!A1:Z26",5,False)"
End If
End Sub

--
Regards,
Tom Ogilvy


"DoctorG" wrote in message
...
Tom, thanks a lot. I didn't know if this was something I couldn't figure
out
or something that can't be done - directly anyway.

Your last suggestion has probably shown me the way, though. Since the
actual
table to be searched relies on the content of a field, I will write a
routine
to change the formulas that refer to the external table to a specific
reference instead of the variable one and link it to an OnChange event in
the
controlling field.

Now I only have to figure out where and how one codes an OnChange event
for
a cell!!! This is something I haven't had to do before. Can you help? I
cannot find cell events anywhere...

"Tom Ogilvy" wrote:

As stated in the help on Indirect, it does not work with a reference to
a
closed workbook. Open the workbook and change your string to the
workbook
name and it will work. Using a defined name does not solve this.

Harlan Grove has written a UDF that works in this situaiton by opening a
second instance of excel, opening the closed file, getting the
information,
closing the file, closing the second instance of excel and returning the
information. I have never used it, so I can't imagine what the speed
penalty must be.

Another alternative is have event driven code actually change the hard
coded
reference.


--
Regards,
Tom Ogilvy


"DoctorG" wrote in message
...
Does anyone know how we can create an external reference from a string
value
"pointing" to the external table?

Example
----------
A1=my_internal_table (content is text)
A2='C:\EXCELFILE.xls'!my_external_table (content is again text)

index(my_internal_table;2;1) --- works
index(indirect(A1);2;1) --- produces same result as previous
index('C:\EXCELFILE.xls'!my_external_table;2;1) --- works
index(indirect(A2);2;1) --- does not work (#REF!)