Also see Chip Pearson's page on Events

http://www.cpearson.com/excel/events.htm

Also, if you change a cell value in a change event, you need to disable
events. I didn't in my routine since the If condition screens out changes
made in cells other than B9, but the caution is in General

Application.enableevents = False
' code to change chells
Application.EnableEvents = True

in reality you should add error handling

On Error goto ErrHandler


.. . .

Application.EnableEvents = False

.. . .
ErrHandler:
Application.EnableEvents = True

this ensures that events get reenabled if you have an error in your routine.
(otherwise, you events may suddenly stop working)

--
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!)