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:
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!
jostafewSystems AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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?

jostafewSystems AdministratorAuthor Commented:
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!
jostafewSystems AdministratorAuthor Commented:
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.....
Hi again

Thanks for those files - as I thought it was a data inconsistency.  Column A in your database spreadsheet contains a mix of numeric values and string 'numbers' - the Label file only contained 'string' numbers so there was a mismatch.

Now, I was able to get it to work by converting your data in the database file by the following:

1. Select column A and go Data>TextToColumns
2. Hit Next on the first screen of the Text Import Wizard and Next again.  On the third screen under Column Date Format, select Text and click Finish.

Hit F9 to recalculate your sheet if necessary.  The formulas should now work.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jostafewSystems AdministratorAuthor Commented:
Thanks Richard, that did the trick!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.