Cant use indirect() and dynamic ranges together?
 

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

Cant use indirect() and dynamic ranges together?
 

This worked for me using

=INDEX(INDIRECT(C1&C2),1,1)

--
__________________________________
HTH

Bob

"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

Cant use indirect() and dynamic ranges together?
 

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

Cant use indirect() and dynamic ranges together?
 

TryThis refers to =OFFSET(Sheet1!$A$1,0,0,10,1)

C1 = Try
C2 = This

=INDEX(INDIRECT(C1&C2),1,1)

I get #REF!

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...
This worked for me using

=INDEX(INDIRECT(C1&C2),1,1)

--
__________________________________
HTH

Bob

"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

Cant use indirect() and dynamic ranges together?
 

Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula.

His *relative* column reference in the Offset formula makes the calculating
range location relative to the location of any formula using the named
range - dependant on which column was in focus during the creation of the
named range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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

Cant use indirect() and dynamic ranges together?
 

there's nothing "static" about his formula.

Nothing?

Assuming you're correct about the relative column, the height and width is
still static!

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula.

His *relative* column reference in the Offset formula makes the
calculating range location relative to the location of any formula using
the named range - dependant on which column was in focus during the
creation of the named range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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

Cant use indirect() and dynamic ranges together?
 

C'mon Biff, we're talking about location (address) here.

You are getting a bit like Harlan now!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
there's nothing "static" about his formula.

Nothing?

Assuming you're correct about the relative column, the height and width is
still static!

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula.

His *relative* column reference in the Offset formula makes the
calculating range location relative to the location of any formula using
the named range - dependant on which column was in focus during the
creation of the named range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"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

Cant use indirect() and dynamic ranges together?
 

You are getting a bit like Harlan now!<bg

That's a tremendous compliment to me but I'm sure Harlan would balk! Really,
I'm not worthy. <g

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
C'mon Biff, we're talking about location (address) here.

You are getting a bit like Harlan now!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
there's nothing "static" about his formula.

Nothing?

Assuming you're correct about the relative column, the height and width
is still static!

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula.

His *relative* column reference in the Offset formula makes the
calculating range location relative to the location of any formula using
the named range - dependant on which column was in focus during the
creation of the named range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"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

Cant use indirect() and dynamic ranges together?
 

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

Cant use indirect() and dynamic ranges together?
 

I did say a *bit*.

Harlan has his bad bits and his good bits, though his good bits do far
outnumber his bad bits.<vbg
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"T. Valko" wrote in message
...
You are getting a bit like Harlan now!<bg

That's a tremendous compliment to me but I'm sure Harlan would balk! Really,
I'm not worthy. <g

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
C'mon Biff, we're talking about location (address) here.

You are getting a bit like Harlan now!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"T. Valko" wrote in message
...
there's nothing "static" about his formula.

Nothing?

Assuming you're correct about the relative column, the height and width
is still static!

--
Biff
Microsoft Excel MVP


"RagDyer" wrote in message
...
Unless he made a typo in his example, there is something special, and
there's nothing "static" about his formula.

His *relative* column reference in the Offset formula makes the
calculating range location relative to the location of any formula using
the named range - dependant on which column was in focus during the
creation of the named range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"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

Cant use indirect() and dynamic ranges together?
 

Ok, my VBA is bad, very bad, but I have a 'partial' solution. Currently, I
have it set to a macro that I run, but because of my bad VBA, it still shows
the blinking dashes around the 'handwritten formula' from the VBA copy.

Building off the TryThis example, with Try in C1, This in C2, also row in
E1, column in E2.
In a 'dummy' cell, I have the following (my dummy cell is H8).
="="&LEFT($C$1,3)&LEFT($C$2,4)
TryThis is defined as:
=INDEX(OFFSET('Sheet1'!$A$1,0,0,10,1),'Sheet1'!$E$ 1,'Sheet1'!$E$2)

And currently, my macro coding is as follows:

Sub ConvertStringToFormula()
Range("H8").Copy
With ActiveCell
.PasteSpecial
ActiveCell.Formula = ActiveCell.Text
Range("H9").Select
End With
End Sub

As I said, it's crude, if you know more than me about VBA (which shouldn't
be difficult), you can modify to suit your needs.




--
John C


"T. Valko" wrote:

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

Cant use indirect() and dynamic ranges together?
 

my VBA is bad, very bad

You should see mine!.

--
Biff
Microsoft Excel MVP


"John C" <johnc@stateofdenial wrote in message
...
Ok, my VBA is bad, very bad, but I have a 'partial' solution. Currently, I
have it set to a macro that I run, but because of my bad VBA, it still
shows
the blinking dashes around the 'handwritten formula' from the VBA copy.

Building off the TryThis example, with Try in C1, This in C2, also row in
E1, column in E2.
In a 'dummy' cell, I have the following (my dummy cell is H8).
="="&LEFT($C$1,3)&LEFT($C$2,4)
TryThis is defined as:
=INDEX(OFFSET('Sheet1'!$A$1,0,0,10,1),'Sheet1'!$E$ 1,'Sheet1'!$E$2)

And currently, my macro coding is as follows:

Sub ConvertStringToFormula()
Range("H8").Copy
With ActiveCell
.PasteSpecial
ActiveCell.Formula = ActiveCell.Text
Range("H9").Select
End With
End Sub

As I said, it's crude, if you know more than me about VBA (which shouldn't
be difficult), you can modify to suit your needs.




--
John C


"T. Valko" wrote:

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