[SOLVED] External table/range reference trouble

[DoctorG]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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!)
>

[DoctorG]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!)
> >
>
>
>

[DoctorG]

Could I use the OnTime approach you suggested to VVaidya for the flashing
cell and check if the contents of cell so-and-so have changed or is there a
more appropriate way?

"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!)
> >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!)
> > >
> >
> >
> >

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!)
> > >
> >
> >
> >

[DoctorG]

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").Value & ";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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!)
> >
>
>
>