I am trying out your idea to change the hardcoded reference in a
Worksheet_Changed event. I am posting my code that will work if I change the
cell formula to i.e. "=TODAY()+"&line_number but will produce a run-time
error if I try to input a formula with a VLOOKUP, either internal or external.
Can you think of what might be wrong or should we assume it's an Excel
limitation??
Bear in mind that if I input the VLOOKUP formula without the initial "=", as
a string, and then manually add the "=" in front, the result is correct -
therefore the syntax of the VLOOKUP is ok.
....
Application.EnableEvents = False

Range("INVOICE_START").Select
ActiveCell.Offset(0, 5).Activate // this is the item
description col

line_number = 1
While line_number <= max_line

' this produces a run-time error...
lookup_formula = "=VLOOKUP(B" & (line_number + 8) & ";" &
Worksheets("Tables").Range("wcCurrentControlDB").V alue & ";2)"
' also this....
lookup_formula = "=vlookup(wcSupplier;D_SUPPLIERS;2)"
' this works...
lookup_formula = "=today()+" & line_number

ActiveCell.Offset(line_number, 0).Formula = lookup_formula
line_number = line_number + 1
Wend


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