External References in Excel; Randomly Missing Data
Hello, I'm working with a rather large excel spreadsheet which contians some columns of data and 2 additional columns which refer to another workbook. Here's an example of the formula I'm using:
=VLOOKUP($A16,'P:\LabelPri
The main workbook is called LabelDatabase.xls, and the externally referenced book is Label.xls. The problem I'm having is that my ex. refs. in LabelDatabase.xls seem to be randomly missing groups of rows in the Label.xls. I've confirmed that all the cells in Label.xls are formatted the same. I thought perhaps I was reaching some sort of cap in size of my workbook (I'm up to row 14,200), but changes I make seemingly anywhere in Label.xls are reflected in LabelDatabase.xls, it's simply returning #N/A for some rows which DO contain data in Label.xls. Assistance would be appreciated, thanks!
=VLOOKUP($A16,'P:\LabelPri
The main workbook is called LabelDatabase.xls, and the externally referenced book is Label.xls. The problem I'm having is that my ex. refs. in LabelDatabase.xls seem to be randomly missing groups of rows in the Label.xls. I've confirmed that all the cells in Label.xls are formatted the same. I thought perhaps I was reaching some sort of cap in size of my workbook (I'm up to row 14,200), but changes I make seemingly anywhere in Label.xls are reflected in LabelDatabase.xls, it's simply returning #N/A for some rows which DO contain data in Label.xls. Assistance would be appreciated, thanks!
ASKER
Ok, I've attached abbreviated versions of each file. They should include sections of data that work and don't. You'll have to fix up the formulae to work on your machine (my files reside on a network drive), but they copies are still exibiting the problem so hopefully you'll spot something!
Label---Copy.xls
LabelDatabase---Copy.xls
Label---Copy.xls
LabelDatabase---Copy.xls
ASKER
Fooling around with the copies I found that if I re-type the ItemNum (lookup key) in LabelDatabase then it will pull in the data. Something goofy going on there.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Richard, that did the trick!
It probably is a data inconsistency - are these text or numeric values you are looking up? Could it be you have leading or trailing spaces in either looked up or lookup data? Or perhaps you have a numeric value in one that is failing to match to a text value in the other?
It's very difficult to be any more precise without actually seeing a sample of the data that isn't working - could you upload a sample of each file showing at least one row which you think exists in both but produces a #N/A error?
Richard