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
|