Problem with VLOOKUP with # of rows

[FloridaHockeyGuy]

I am using VLOOKUP to access an EXTERNAL .xls file and pull in data from it. However, I have found out that when the data in the external database is on the 16,126th row or higher, it will return N/A in all the fields in my input file. Anything in rows 1-16125 of data, it works flawlessly.

Here is a typical cell with the VLOOKUP (there are 4 of them for different columns):

=IF(A20 <>"",VLOOKUP(K20,'C:\Catalog\[2008 Data.xls]Data'!$A:$F,6,FALSE),"")

The external database is obviously called 2008 Data.

I have never encountered this, and I hope that somebody can shed some light on this, as it has become a source of major frustration. Thanks in advance for your help!

 

[FloridaHockeyGuy]

Sorry, one thing I forgot to mention. If I open the external database, then all the N/A turn into the correct data. So, the problem exists only in those rows when the external database is closed, but disappears when it is opened. The only problem is that I would prefer that the users NOT open the source database to prevent them from screwing things up inadvertently, although I suppose I can protect the document. Just a pain to have them open two spreadsheets.

 

[hkaplan2]

Have you tried explicitly naming the data range, rather than just the columns (i.e. A1:F20000 for example instead of $A:$F).

Or using Offset to name a dynamic range, such as:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Naming a dynamic range allows you to append to the end of the table without redefining the range or referencing just the columns, as you did.

Just a thought.

 

[Matty]

It seems very odd that the lookups work when the external file is open, but not when it is closed...

Are you sure that the Worksheet it full 'recalculated' when the external Workbook is closed?

Press F9 to test this - you will see your Worksheet calculating up to 100% in the bottom left of the screen (on the Status Bar).

Matty

 

[FloridaHockeyGuy]

Have you tried explicitly naming the data range, rather than just the columns (i.e. A1:F20000 for example instead of $A:$F).

Or using Offset to name a dynamic range, such as:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

Naming a dynamic range allows you to append to the end of the table without redefining the range or referencing just the columns, as you did.

Just a thought.

Yes, tried naming the data range, but no difference. I have never used OFFSET, therefore not really familiar or comfortable with it, I have to confess I am not sure how I would incorporate it into the formula instead of VLOOKUP. I will try to my homework and report back.

Thanks.

 

[FloridaHockeyGuy]

It seems very odd that the lookups work when the external file is open, but not when it is closed...

Are you sure that the Worksheet it full 'recalculated' when the external Workbook is closed?

Press F9 to test this - you will see your Worksheet calculating up to 100% in the bottom left of the screen (on the Status Bar).

Matty

Yes, it is. I notice, though, that when I try to make changes, I get the dreaded Excel memory error message, likewise when I open and sometimes close the external database. Just wondering if maybe the memory Excel uses is taxed more when the external database is closed, and some is liberated when open. I guess one possible solution would be to put a macro (I have to try to make this as idiot proof as possible for the users) in the input working spreadsheet to open the external database at the same time, and then close it when the input file is closed. Either that, or else I will have to split the external database into 2 separate databases and make them smaller, although I think that would be more of a headache.

 

[hkaplan2]

You use Insert | Name | Define to give a name to your range (i.e. "mydatarange"). And in the Refers to box in the dialog box, use the formula I gave you, edited to suit your needs.

Then your vlookup uses the range name instead of your $A:$F reference.

Such as =vlookup(A1,mydatarange,6,false).

It may not change your result but it is worth a try.

 

[FloridaHockeyGuy]

You use Insert | Name | Define to give a name to your range (i.e. "mydatarange"). And in the Refers to box in the dialog box, use the formula I gave you, edited to suit your needs.

Then your vlookup uses the range name instead of your $A:$F reference.

Such as =vlookup(A1,mydatarange,6,false).

It may not change your result but it is worth a try.

Well, if nothing else, I learned how to do this, which should be valuable going forward with other projects. Alas, it worked with the ones that worked before and did not work with the ones that did not. So back to square one. Could it be that Excel will only work with external CLOSED databases up to a certain limit of data? I just find it out that it works flawlessly for all rows when the other workbook is open. Very frustrating. But I appreciate all the help and tips. Maybe time to take break and look at it again fresh in the morning.