How To Link To An External Dynamic Named Range

[Minitman]

Greetings,

I am attempting to get data from a workbook called MCL.xls. I am in a
workbook called wb2.xls. MCL.xls is my customer data. I made a sample
of MCL.xls with the name wb1.xls which contains a sheet called
CustList (the same as in MCL.xls). I then copied and pasted the first
ten rows of the MCL.xls CustList to the wb1.xls CustList. Finally I
copied and pasted the dynamic named ranges MCL_Name (1st column only)
and MCL_All (All 71 columns of the customer list) into wb1.xls
insert>define>name section.

wb2 is a limited sample of my monthly schedulers. It is the workbook
looking for the data from the links to MCL or wb1.

The value in column AI is used as the source for the Vlookup in column
D. The data validation is using the named range MCL_Name which is
applied to this column indirectly thru a local named range called
MyList_3 (Validation cannot access external links directly).. Here is
the definition of MyList_3 in wb2 which is used to access the named
range MCL_Name in MCL indirectly:

=MCL.xls!MCL_Name

I can change which workbook this code is looking to by changing
MyList_3 to this:

=wb1.xls!MCL_Name

Both of these are working.

My problem is with the Vlookup. Since Vlookup can access external
named ranges directly and indirectly, I chose the direct route and
used this code in row 3 of column D looking to wb1.xls:

=IF(AI3="","",VLOOKUP(AI3,wb1.xls!MCL_All,2,FALSE))

This works fine. But when I point this code at the MCL workbook like
this:

=IF(AI3="","",VLOOKUP(AI3,MCL.xls!MCL_All,2,FALSE))

I get the #REF error. This error is supposedly telling me that there
is no named range called MCL_All in the workbook called MCL! When I
check the named ranges in MCL, I see MCL_All and the definition is
correct! Again, this is the definition I had copied and pasted into
the named range section of wb1 with the same name, where it is
working.

Any one have any ideas as to what is going on????

Any ideas, help or pointers are greatly appreciated.

Thanks for taking the time to look at my problem. If you would like a
copy of these three workbooks just let me know and I'll send them to
you.

-Minitman

 

[Minitman]

I think I found the problem.

In trying to figure out why the link worked on one workbook and not
another, I tried coping more of the records from MCL into wb1.
Everything was still working at record 2502. At record 2503 I all of
sudden got that #REF error. When I removed that last record then
everything was working again. There are 3000 records in this list.

Anyone have a solution or a work around for this limitation?

Any help is greatly appreciated.

-Minitman