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" wrote in message
...
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



On Thu, 16 Aug 2007 08:22:26 +0100, "Bob Phillips"
wrote:

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)