Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
Hello - are there limitations around having the 'Table Array' in the vlookup
formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
I tested with Excel 2007...
The value got update even when the source file was closed. I had to enable 'Automatic update of links'.... "Kathy L." wrote: Hello - are there limitations around having the 'Table Array' in the vlookup formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
Thank you...unfortunately I'm on Excel 2003, sorry I should have mentioned.
I do have the automatic update of links enabled in the options. "Sheeloo" wrote: I tested with Excel 2007... The value got update even when the source file was closed. I had to enable 'Automatic update of links'.... "Kathy L." wrote: Hello - are there limitations around having the 'Table Array' in the vlookup formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
As far as I know, VLOOKUP works with closed file also...
INDIRECT requires the other file to be open. "Kathy L." wrote: Thank you...unfortunately I'm on Excel 2003, sorry I should have mentioned. I do have the automatic update of links enabled in the options. "Sheeloo" wrote: I tested with Excel 2007... The value got update even when the source file was closed. I had to enable 'Automatic update of links'.... "Kathy L." wrote: Hello - are there limitations around having the 'Table Array' in the vlookup formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
What's your formula look like:
When the source file is closed Excel "complains" if the table_array is more than 16375 rows. If your using something like this: =VLOOKUP(A1,'C:\TV\[xxx.xls]Sheet1'!A:B,2,0) Use a smaller specific range in the table_array if you can. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... Hello - are there limitations around having the 'Table Array' in the vlookup formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup issues when source data is in another workbook?
Thank you...good tip. My formula did look like this, but now I added in the
specific rows (2 through 1260) in the table_array. Unfortunately, this morning, it seems the Update still isn't pulling as I'd expect. Refreshes fine once I open the data file, but when I first open and select Update Data, I still get the message that says "This workbook contains one or more links that cannot be udpated." "T. Valko" wrote: What's your formula look like: When the source file is closed Excel "complains" if the table_array is more than 16375 rows. If your using something like this: =VLOOKUP(A1,'C:\TV\[xxx.xls]Sheet1'!A:B,2,0) Use a smaller specific range in the table_array if you can. -- Biff Microsoft Excel MVP "Kathy L." wrote in message ... Hello - are there limitations around having the 'Table Array' in the vlookup formula be another workbook? In other words, does the other workbook have to be open to work, or any other limitation? I ask, because I'm trying to have a vlookup to another workbook, and do not want users to have to open this other workbook. When they open the file with the vlookup formula, we are getting the prompt asking if they want to Update the data. When I select Update, it doesn't seem to work. It seems to only show the values from the vlookup from the last time I saved the file, or works if I open the other workbook with the destination data. I hope that makes sense. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem: Paste Data after Source Workbook in Closed... | Excel Discussion (Misc queries) | |||
Issues with Linking Data in Various Tabs within a workbook? | Excel Worksheet Functions | |||
Data Source Trust Issues | Excel Discussion (Misc queries) | |||
pivot w/ a different workbook as data source - odd behavior | Excel Discussion (Misc queries) | |||
VLOOKUP - recover missing data source | Excel Worksheet Functions |