Hi,

The variable rng_MGXdataSemiAnnual exists in the dropdown box in the
worksheet and refers to a matrix in the worksheet. There is a similar
matrix on a number of different worksheets, each with its own name

In the VBA, I define a new variable rng_MXGdata:

rng_MXGdata = [rng_MXGdataSemiAnnual]

This new variable is not recognised as a name in the names collection.
I tried using the following:

Names.Add Name:="rng_MGXdata", RefersToLocal:=rng_MGXdata

but it doesn't seem to make rng_MXGdata appear in the dropdown box.
Perhaps this is because you can't have two names referring to the same
ranges? Do I have to delete rng_MXGdataSemiAnnual from the names
collection before rng_MGXdata can be recognised? I need it as part of
the name collection so I can use it in commands like the following:

Offset(3).Formula = "=VLOOKUP(R[-3]C,rng_MXGdata,3,FALSE)"

I will also need to finally delete rng_MXGdata from the "workspace" and
restore the original rng_MXGdataSemiAnnual so if anyone can suggest how
to do go about this I would be grateful.

Your help is appreciated.

Thanks,

Edward


--
brachistochrone
------------------------------------------------------------------------
brachistochrone's Profile: http://www.excelforum.com/member.php...o&userid=34274
View this thread: http://www.excelforum.com/showthread...hreadid=540353