Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Calculation from another Excel file
At one point in one of my macros, I open another Excel File (I will call this
newly opened file FileO to make this read easier). The macro then changes a Vlookup formula in the current file to link to a range in FileO. The formula is then Re-Calculated. The macro then closes FileO. And the macro stops, with the current file still open. When I close FileO the current file's Vlookup formula gives a reference error, if I recaluclate. But it works fine if FileO is open. Is there an option I need to change? Or is this something that happens when you use a Vlookup to another file? I have not used many Vlookups with other files in the past, normally just the current file. Thank You, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Calculation from another Excel file
Are you using Indirect in your formula. Indirect doesn't work with closed
workbooks. -- Regards, Tom Ogilvy "tjh" wrote in message ... At one point in one of my macros, I open another Excel File (I will call this newly opened file FileO to make this read easier). The macro then changes a Vlookup formula in the current file to link to a range in FileO. The formula is then Re-Calculated. The macro then closes FileO. And the macro stops, with the current file still open. When I close FileO the current file's Vlookup formula gives a reference error, if I recaluclate. But it works fine if FileO is open. Is there an option I need to change? Or is this something that happens when you use a Vlookup to another file? I have not used many Vlookups with other files in the past, normally just the current file. Thank You, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Calculation from another Excel file
Yes, I am using Indirect to reference the range in the other workbook through
the vlookup. The Indirect($AZ$1) is the file and range name. =VLOOKUP($A11,INDIRECT($AZ$1),O$6,FALSE) Is there another easy workaround to the Indirect problem, or would I need to change each of these formulas throught the VBA code. Thank You, "Tom Ogilvy" wrote: Are you using Indirect in your formula. Indirect doesn't work with closed workbooks. -- Regards, Tom Ogilvy "tjh" wrote in message ... At one point in one of my macros, I open another Excel File (I will call this newly opened file FileO to make this read easier). The macro then changes a Vlookup formula in the current file to link to a range in FileO. The formula is then Re-Calculated. The macro then closes FileO. And the macro stops, with the current file still open. When I close FileO the current file's Vlookup formula gives a reference error, if I recaluclate. But it works fine if FileO is open. Is there an option I need to change? Or is this something that happens when you use a Vlookup to another file? I have not used many Vlookups with other files in the past, normally just the current file. Thank You, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Calculation from another Excel file
If the workbook being referenced is close, then there is no easy workaround
to the indirect problem. You can search the groups for a post by Harlan Grove where he posts his PULL function which is a User defined function which will do this. However, this function opens the referenced workbook in another instance of excel, retrieves the data and then closed it and the instance of excel. I doubt that it is very fast if you need a number of cells to have the formula. The other way is to use event code to update the formula with a hard coded link. -- Regards, Tom Ogilvy "tjh" wrote in message ... Yes, I am using Indirect to reference the range in the other workbook through the vlookup. The Indirect($AZ$1) is the file and range name. =VLOOKUP($A11,INDIRECT($AZ$1),O$6,FALSE) Is there another easy workaround to the Indirect problem, or would I need to change each of these formulas throught the VBA code. Thank You, "Tom Ogilvy" wrote: Are you using Indirect in your formula. Indirect doesn't work with closed workbooks. -- Regards, Tom Ogilvy "tjh" wrote in message ... At one point in one of my macros, I open another Excel File (I will call this newly opened file FileO to make this read easier). The macro then changes a Vlookup formula in the current file to link to a range in FileO. The formula is then Re-Calculated. The macro then closes FileO. And the macro stops, with the current file still open. When I close FileO the current file's Vlookup formula gives a reference error, if I recaluclate. But it works fine if FileO is open. Is there an option I need to change? Or is this something that happens when you use a Vlookup to another file? I have not used many Vlookups with other files in the past, normally just the current file. Thank You, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup Calculation from another Excel file
Thank You for your response.
1) I wanted to try out the Pull function that Harlan created so, I placed it into a module, but I am still not sure how to make it work. Do I need to alter the Vlookup or the value within the indirect cell that refers to the other excel file and range? 2) How would you suggest using event code? Thank You, "Tom Ogilvy" wrote: If the workbook being referenced is close, then there is no easy workaround to the indirect problem. You can search the groups for a post by Harlan Grove where he posts his PULL function which is a User defined function which will do this. However, this function opens the referenced workbook in another instance of excel, retrieves the data and then closed it and the instance of excel. I doubt that it is very fast if you need a number of cells to have the formula. The other way is to use event code to update the formula with a hard coded link. -- Regards, Tom Ogilvy "tjh" wrote in message ... Yes, I am using Indirect to reference the range in the other workbook through the vlookup. The Indirect($AZ$1) is the file and range name. =VLOOKUP($A11,INDIRECT($AZ$1),O$6,FALSE) Is there another easy workaround to the Indirect problem, or would I need to change each of these formulas throught the VBA code. Thank You, "Tom Ogilvy" wrote: Are you using Indirect in your formula. Indirect doesn't work with closed workbooks. -- Regards, Tom Ogilvy "tjh" wrote in message ... At one point in one of my macros, I open another Excel File (I will call this newly opened file FileO to make this read easier). The macro then changes a Vlookup formula in the current file to link to a range in FileO. The formula is then Re-Calculated. The macro then closes FileO. And the macro stops, with the current file still open. When I close FileO the current file's Vlookup formula gives a reference error, if I recaluclate. But it works fine if FileO is open. Is there an option I need to change? Or is this something that happens when you use a Vlookup to another file? I have not used many Vlookups with other files in the past, normally just the current file. Thank You, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file link with two worksheet calculation | Excel Worksheet Functions | |||
Vlookup calculation | Excel Worksheet Functions | |||
Use VLOOKUP in a sumproduct calculation | Excel Discussion (Misc queries) | |||
Calculation using VLookup | Excel Worksheet Functions | |||
Formula calculation in a Shared Excel File | Excel Discussion (Misc queries) |