I have a vlookup function linked to a Table in an external workbook. But when the external workbook is closed the formulas result in #Ref! errors.
How do i overcome this problem. I am linking my vlookup to table to make the range dynamic as my external workbook table data keeps on growing.
=IFERROR(Your_formula,"")
should do that.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I am looking to find a way to update the values while the workbook is closed.
Your suggestion is good when the external link is not dynamic.
Ah.. Unfortunately, dynamic named ranges do not work on closed workbooks.
Its not a names range, but a Table. So you mean it doesn't make any difference if its a named range or a table the result is same ???
Is it necessary for the external reference to be dynamic in size? I would try a fixed reference that is larger than the source lookup table will ever be. If I know I will never have more than 100 records in the source, use [book]sheet!$A$1:$F$100 for the lookup table reference.
Originally Posted by shg
Yes my source lookup table is being populated on a daily basis and keeps growing.
I can easily fix a larger range as you suggested into my vlookup function but it drastically slows down the calculation process as my excel destination file has large data running over half a million rows at any given time.
Anyhow thanks for your input.
Can you show the formula/s you are having problems with?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I have the following vlookup in 6 different columns across over 625000 rows:
=VLOOKUP(B625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dTable_Stores_Geo_Master[#Data],2,0)
=VLOOKUP(B625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dTable_Stores_Geo_Master[#Data],3,0)
=VLOOKUP(B625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dTable_Stores_Geo_Master[#Data],4,0)
=VLOOKUP(B625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dTable_Stores_Geo_Master[#Data],9,0)
=VLOOKUP(D625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dState[#Data],3,0)
=VLOOKUP(D625370,'D:\Imran\SQL Reports\Imran\AllAnalysisMaster.xlsm'!dState[#Data],5,0)
I suspect that you are pushing the limits of Excel... You will already have thought of this... but can't you do the lookups in AllAnalysisMaster.xlsm
I don't know how much of a time penalty is associated with accessing a closed workbook. I note that your formulas are using linear "exact match" searches (4th argument=0 or FALSE), and I know there is a severe time penalty for linear searches. A third observation -- you have 4 functions performing the same lookup (which row contains the value in B625370) and 2 functions performing the "which row contains the value in D625370". Working backwards:
1) Reduce or eliminate duplicated effort by replacing those 6 lookups with 2 lookups. You perform the 2 lookups with MATCH() functions (in helper cells), then use INDEX() to return each needed field from the found row. See this discussion for details: https://www.excelforum.com/excel-for...ows-excel.html
2) By any means necessary, sort the lookup table so you can use a binary search. If I had 0.5E6 rows to search through, I think it would take threats to job, family, life, national security, or similar to convince me to use a linear search. The time/performance penalty associated with linear searches is so great with large data sets that I would do all I could to make this a binary search. Note that, if "exact match" is important, it will almost certainly be faster to perform 2 binary searches than 1 linear search. (=IF(B625370=VLOOKUP(...,TRUE),VLOOKUP(...,TRUE),NA()) will be faster than the current formulas).
I created a pair of simple worksheets to test (lookup file contained a full column 1E6 rows of data. working file contained ~450 copies of a random lookup on this column of data). Testing results:
search type -- lookup file state -- calculation time
linear -- closed -- ~10 seconds
linear -- open -- ~5 seconds
binary -- closed -- near instantaneous
binary -- open -- instantaneous
Based on the limited information you have provided, I would try those strategies and see if performance improves enough to be workable.
Thank you so much. i will try out your suggestion.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks