Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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" 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!) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
External table/range reference trouble
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!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic named range reference from external workbook | Excel Discussion (Misc queries) | |||
Please Help - Trouble with counting occurrences in external file | Excel Worksheet Functions | |||
Pivot Table range source area reference | Excel Discussion (Misc queries) | |||
External Data Range Import to a Cell not Table | Excel Discussion (Misc queries) | |||
Trouble with making a 3D reference absolute | Excel Discussion (Misc queries) |