Here's how it can be done using CHOOSE.

Assume you have named ranges defined using OFFSET:

RngOne
RngTwo

A1 = Rng
A2 = Two

=INDEX(CHOOSE(MATCH(A1&A2,{"RngOne","RngTwo"},0),R ngOne,RngTwo),1)

Note that you're limited to the number of ranges that CHOOSE can handle
depending on which version of Excel you're using. In Excel 2007 you can have
up to 254 ranges. In other versions it's limited to 29.

If you have more than a few ranges you can create a table and MATCH the name
in the table rather than hardcode the names as I've done in the above
formula.

J1 = RngOne
J2 = RngTwo

=INDEX(CHOOSE(MATCH(A1&A2,J1:J2,0),RngOne,RngTwo), 1)

Although I've never had to do it, I imagine there should be some way to make
this work if you have more ranges than CHOOSE can handle. Probably a nested
CHOOSE CHOOSE combination would work.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
INDIRECT requires a text representation of a *valid reference*. The OFFSET
formula does not meet this requirement. When you call SGDCalendar you're
actually calling the named OFFSET *formula* which is not a text
representation of a valid reference.

About the only way I know of to get around this invovles using the CHOOSE
function. However, since you're concatentating strings to build the named
range this adds complexity to the mix. I love complexity! <g

There's nothing "special" about your named range, it's just a static
range. Why don't you just use:

SGDCalendar =Sheetname!$A$1:$A$4000

Replace "Sheetname" with the actual sheet name.

--
Biff
Microsoft Excel MVP


"nashism" wrote in message
...
Hi,

I have defined a range using the following syntax:

SGDCalendar=Offset(A$1,0,0,4000,1) in a worksheet

I have similarly defined other names like MYRCalendar, IDRCalendar
primarily because I have other data that is labeled starting SGD, MYR
etc that I would like to combine this with.

now when I try to get a value from the range as INDEX(SGDCalendar,1,1)
it works correctly

but if I do something Iike INDEX(Indirect("SGD"&"Calendar"),1,1) it
gives me an #REF! error (note that "SGD" and "Calendar" will be
strings that will come from elsewhere in my workbook)

I have troubled many people around me to solve this but cant seem to
get any further. Any help would be greatly appreciated!

ciao
Nash