Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks

[Minitman]

Greetings,

I have two workbooks (wb1 and wb2). I have a named range on wb2
called MyList. It is 71 columns by 3000 rows on a sheet called
CustList. The dynamic named range is defined in the
Insert>Name>Define window with this code:

=OFFSET(CustList!$A$2,0,0,COUNTA(CustList!$A:$BS),71)

The formula I need is to get the value in the 2nd column of MyList on
wb2 when the value in $AI$2 on wb1 is the same as the entry in the 1st
column of MyList on wb2.

I can get this to work if MyList is in the same workbook as the
formula. I am trying to get rid of individual customer lists in favor
of a centralized list.

This is the code I use for getting the value from a customer sheet on
wb1:

=IF(AI2="","",VLOOKUP(AI2,MyList,2,FALSE))

This works until I tried to modify it like this:

=IF(AI2="","",VLOOKUP(AI2,wb2.xls!MyList,2,FALSE))

I really need to get the data from wb2 not wb1.

Any one have any ideas as to how to accomplish this?

TIA

-Minitman

 

[Bob Phillips]

Create a new named list on wb1 referring to the named range on wb2

=wb2!myList

and use that name in wb1's formulae.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Minitman]

Hey Bob,

Thanks for the reply.

I created the 2 workbooks called wb1.xls and wb2.xls and opened the
workbook called MCL.xls

I copied the first 10 columns of MCL.xls over to wb1.xls on a sheet
called CustList (same name as the sheet in MCL.xls)

I then copied the first 28 rows of the sheet called Input (this is a
monthly scheduling workbook) into sheet 1 of wb2. The VLOOKUP's are
all on wb2 and will be doing all of the calling for these named
ranges.

There are 4 columns of interest in wb2 - column AI is the lookup_value
for the three VLOOKUP's in columns D, E & F. Column AI gets it's
value from a validation drop down using a local named range as the
source for it's list. The local named range is called MyList_3 and is
tied to an external dynamic named range called MCL_Name on either
wb1.xls or MCL.xls. I change the first three characters (wb1 to MCL
or MCL to wb1 as needed) in the named range area of wb1 to change
where it is pointing

I created named ranges on both workbooks (MCL and wb1) to match the
named ranges being called for in the monthly scheduler and wb2.xls. I
called them MCL_Array

This is working with wb1 & wb2.

But when I transported the named ranges from looking at wb1.xls to
MCL.xls that is when the VLOOKUP's return the #REF error. So I check
the named ranges in MCL.xls and wb1.xls, they are identical. I know
the $REF error is telling me that there is no MCL_Array in MCL.xls,
but it is there!

I am at a loss as to what to do next.

Anyone have any ideas?

Any help is appreciated.

A copy of the three workbooks is available upon request.

-Minitman

 

[Bob Phillips]

Why don't you send me the workbooks?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Minitman]

Hey Bob,

Thanks for being willing to take a look at my workbooks.

I tried to send you an email. I'm not sure that I have your email
address. If you get it, go ahead and reply and I'll send those
workbooks off to you. Otherwise, I'll need an address that can
receive a 2 meg file (zipped)

I did discover the problem. My formulas are correct, the problem is
in the named range size that I was trying to access.

I increased the size of wb1 up to 2503 rows and the named range still
worked. When I added one more row, it stopped working. It appears
that there are some limitation in using Vlookup that I am not aware
of.

So the question charges into - How can I get around these Vlookup
limitations?

Any ideas, thoughts or solutions are always welcome.

Thanks.

-Minitman

 

[Bob Phillips]

When you do send it, put Excel in the title as it is likely to go in the
spam bin, and I'll find it more easily then.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Minitman]

Hey Bob,

I did a temporary fix by reducing the number of columns in each row
that I was accessing (from 71 to 21) to increase the number of rows I
could access (+2900). This is not a fix, it only a band-aid. It does
give me a little more time (more customers) before I run into this
wall again.

The master customer list is working at this time, just not sure for
how long.

When it runs into that wall again, I'll be looking for a different
solution at that time.

If you or anyone else has any ideas that can get around the
limitation, please le me know.

Thanks for the interest in this problem.

-Minitman