 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008vlookup problems
My vlookup of customer information is working fine until I add a customer to the list. The vlookup formula looks fine and it's referencing the proper worksheet (customer info). The range is fine (I'm assuming if the data range encompasses the entire worksheet it's fine) and the column indexes are correct. Yet, no data is returned to the destination cells. This only started happening after I added some customers to the customer info sheet. I'm not sure if this matters, but I've manually updated the source links also, hoping this would help. It didn't. Are there any other things I should be looking at when troubleshooting? Thank you in advance for your help. Daryl could we see your formula? >My vlookup of customer information is working fine until I add a >customer to the list. The vlookup formula looks fine and it's [quoted text clipped - 8 lines] > >Daryl > could we see your formula? > [quoted text clipped - 12 lines] > > - Show quoted text - =VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ [Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) This vlookup is used for 7 other fields and the only difference is the column index. [Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) [Vendors.xlsx]Vendor List (2)'!$1:$1048576,column(),FALSE)  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com On Feb 22, 1:07 pm, scott <s...@d2.com> wrote: > could we see your formula? > [quoted text clipped - 12 lines] > > - Show quoted text - =VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ [Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) This vlookup is used for 7 other fields and the only difference is the column index. Perhaps I am misunderstanding the formula, but are there columns defined in the range? such as: =VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List (2)'!$A$1:$G$1048576,3,FALSE) ? >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) $1:$1048576 defines every row from 1 through 1048576 and thus every column in each row Tyro > Perhaps I am misunderstanding the formula, > but are there columns defined in the range? [quoted text clipped - 7 lines] >>=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ >>[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) Tyro explained that the original range included all the cells in the worksheet, but I agree with your point. I'd also limit the number of columns to just what I need. I'd use something like: $a:$g Then the formula wouldn't think it would have to recalculate when I changed something out side the range I needed--like in column X. > Perhaps I am misunderstanding the formula, > but are there columns defined in the range? [quoted text clipped - 7 lines] > >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ > >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) SignatureDave Peterson I was actually ignorant on the columns being defined this way. Is it possible that with such a huge range, calculation has never been completed? Just grasping to understand. Thanks scott >Tyro explained that the original range included all the cells in the worksheet, >but I agree with your point. [quoted text clipped - 17 lines] >> >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ >> >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) $1:$1048576 involves 1,048,576 rows * 16,384 columns = 17,179,869,184 cells. Do you really need that number of rows and columns in the data for your lookup array? Could you get by with, say 1,000 rows and 10 columns? Or do you really over 17 billion cells involved in your lookup array? Tyro >I was actually ignorant on the columns being defined this way. > Is it possible that with such a huge range, calculation has never been [quoted text clipped - 26 lines] >>> >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ >>> >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) In Excel 2007 A:A defines column A which defines all column A entries in all rows of column A, that is: 1,048,576 rows * 1 column = 1,048,576 cells. 1:1 defines row 1 which defines all row 1 entries for all of the columns in row 1, that is 1 row * 16,384 columns = 16,384 cells Tyro >I was actually ignorant on the columns being defined this way. > Is it possible that with such a huge range, calculation has never been [quoted text clipped - 26 lines] >>> >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ >>> >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) Lots of excel's functions limit themselves to the used range. So I wouldn't be surprised if there wasn't much of a calculation hit in most cases. But I bet you've seen excel not reset its used range after you type something way past the "normal" used range and then deleted/cleared the cell. I think I'd be more worried about how often the function wants to recalculate. If I limit my lookup range to A:G (say) and make changes in H:(lastcolumn), then it won't recalc. Any change in that lookup range is going to make the function (all the functions!) that use that range recalculate. I guess that could be a reason to use =index(match()) instead of =vlookup(). This only depends on a couple of columns: =index(sheet2!z:z,match(a1,sheet2!a:a,0)) instead of using 26 columns: =vlookup(a1,sheet2!a:z,26,false) > I was actually ignorant on the columns being defined this way. > Is it possible that with such a huge range, calculation has never been [quoted text clipped - 24 lines] > >> >=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\ > >> >[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE) SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.