named ranges error

[Rob Hick]

Hi all,

Wonder if someone can help me with a slightly strange named ranges
problem - the bottom range works, the top one does not:

=if((IssueCluster!StartMonth-11)<=0,(OFFSET(IssueCluster!$I$1,0,0,13-IssueCluster!StartMonth,1),OFFSET(IssueCluster!$A$1,0,IssueCluster!DGH_Column-1,(IssueCluster!StartMonth-1)+(IssueCluster!NumMonths-12),1)),
OFFSET(IssueCluster!DGH_Current,-12,0))

=IF((IssueHospital!StartMonth-11)<=0,(OFFSET(IssueHospital!$E$1,0,0,13-IssueHospital!StartMonth,1),OFFSET(IssueHospital!$D$1,0,0,(IssueHospital!StartMonth-1)+(IssueHospital!NumMonths-12),1)),OFFSET(IssueHospital!CurrentMonths,-12,0))

when i enter the top formula in the 'refers to:' box it gets change to
a string of text:

=(IssueCluster!StartMonth-11)<=0
(OFFSET(IssueCluster!$I$1,0,0,13-IssueCluster!StartMonth,1),OFFSET(IssueCluster!$A$1,0,IssueCluster!DGH_Column-1,(IssueCluster!StartMonth-1)+(IssueCluster!NumMonths-12),1))
IssueCluster!DGH_Current

The only difference between the 2 formulas (they should both do the
same thing - select a contiguous range) is that the top one contains
the extra:

OFFSET(IssueCluster!$A$1,0,IssueCluster!DGH_Column-1 ......

instead of

OFFSET(IssueHospital!$D$1,0,0

Is this a known problem or am i being dumb?!

Thanks for wading through - much appreciated

Rob

 

[Rob Hick]

Possibly the code was a bit too long to make any sense of so i'll
rephrase the question. Is there a limit to the number of characters a
named function/range can use? If this limit is reached, does the
named range/formula tend to go a bit nutty?

thanks
Rob

 

[Harlan Grove]

Possibly the code was a bit too long to make any sense of so i'll
rephrase the question. Is there a limit to the number of characters a
named function/range can use? If this limit is reached, does the
named range/formula tend to go a bit nutty?

Define 'nutty'.

(e-mail address removed) (Rob Hick) wrote in message

Wonder if someone can help me with a slightly strange named ranges
problem - the bottom range works, the top one does not:

[reformatted]

=if(
(IssueCluster!StartMonth-11)<=0,
(
OFFSET(
IssueCluster!$I$1,
0,
0,
13-IssueCluster!StartMonth,
1
),
OFFSET(
IssueCluster!$A$1,
0,
IssueCluster!DGH_Column-1,
(IssueCluster!StartMonth-1)+(IssueCluster!NumMonths-12),
1
)
),

Looks like you want the 'TRUE' result to be a multiple area range. Correct?

And the FALSE result is a single area range.

Note: you've got 'if' in lower case, so you didn't paste in the formula from
Excel. Please confirm it's correct.

[reformatted]
Another multiple area range.

[minimally reformatted]
There are no double quotes in this, so it'd be treated as a formula rather than
as a string constant, and it's a syntactically invalid formula. Are you saying
Excel strips off the initial 'if' and completely screws up the FALSE expression?

That's not what the TRUE part is doing. It's creating a multiple area ranges.
Are the two OFFSET calls supposed to be opposite corners of a single area range?
If so, replace (OFFSET(..),OFFSET(..)) with (OFFSET(..):OFFSET(..)).

Anyway, I can't figure out why Excel would be corrupting your first formula
unless what you're *actually* entering for the first formula isn't what you
wrote in your original posting. Even then, I can't see how Excel is giving you
the result you claim unless you inadvertently omitted the double quotes or your
copy of Excel is thoroughly corrupted and needs to be reinstalled.

 

[Rob Hick]

Thanks for your post to me Harlan. When I say "nutty" i mean behave a
little oddly! If i put the 'bad version' formula into the define
named range dialogue box, XL reformats it by stripping off the = sign
and messing around with the final part of the IF function. The really
wierd thing was, it actually worked, i.e. selected a range, but if i
tried to reference that range in another fomula, it simply appeared as
a text string. When i wnet back to look at the formula, it had been
altered as described above.

The formula is supposed to select a range of cells ("comparison
range") 12 rows above another range of cells ("current range") (i.e. a
set of monthly data is selected and this formula selects a 12 month
comparison). The problem comes if there are less than 12 rows above
the "current range", in which case the formula would return an error.
If this is the case, hence the if statement, the formula calculates
the number of rows above the "current range" and then creates a
contiguous range of these cells plus another range which accounts for
the 'missing' rows. This gives me a range of the desired size with
blank cells, which i can use in charts.

The difference between the version that works and the one that does
not is that the 'good' version hardcodes in a specific column to work
on, whereas the 'bad' version uses a reference to the column number
provided by the MATCH function (i.e. it finds the column labelled as
required).

It's all a bit strange really. I haven't had any problems with other
named formulas and i'm using them fairly extensively. Do you really
think i might need to reinstall???!

thanks again for your help
Rob

Possibly the code was a bit too long to make any sense of so i'll
rephrase the question. Is there a limit to the number of characters a
named function/range can use? If this limit is reached, does the
named range/formula tend to go a bit nutty?

Define 'nutty'.

(e-mail address removed) (Rob Hick) wrote in message

Wonder if someone can help me with a slightly strange named ranges
problem - the bottom range works, the top one does not:

[reformatted]

=if(
(IssueCluster!StartMonth-11)<=0,
(
OFFSET(
IssueCluster!$I$1,
0,
0,
13-IssueCluster!StartMonth,
1
),
OFFSET(
IssueCluster!$A$1,
0,
IssueCluster!DGH_Column-1,
(IssueCluster!StartMonth-1)+(IssueCluster!NumMonths-12),
1
)
),

Looks like you want the 'TRUE' result to be a multiple area range. Correct?

And the FALSE result is a single area range.

Note: you've got 'if' in lower case, so you didn't paste in the formula from
Excel. Please confirm it's correct.

[reformatted]
Another multiple area range.

[minimally reformatted]
There are no double quotes in this, so it'd be treated as a formula rather than
as a string constant, and it's a syntactically invalid formula. Are you saying
Excel strips off the initial 'if' and completely screws up the FALSE expression?

That's not what the TRUE part is doing. It's creating a multiple area ranges.
Are the two OFFSET calls supposed to be opposite corners of a single area range?
If so, replace (OFFSET(..),OFFSET(..)) with (OFFSET(..):OFFSET(..)).

Anyway, I can't figure out why Excel would be corrupting your first formula
unless what you're *actually* entering for the first formula isn't what you
wrote in your original posting. Even then, I can't see how Excel is giving you
the result you claim unless you inadvertently omitted the double quotes or your
copy of Excel is thoroughly corrupted and needs to be reinstalled.

 

[Harlan Grove]

It's all a bit strange really. I haven't had any problems with other
named formulas and i'm using them fairly extensively. Do you really
think i might need to reinstall???!

....

Given what you describe (that I've never seen), yes, I do think a reinstall
would be a good idea.

 

[Rob Hick]

...

Given what you describe (that I've never seen), yes, I do think a reinstall
would be a good idea.

desperately attempting to avoid the re-install i think i might have
found the answer. There is a limit of 256 characters on the named
formulas/ranges. I was only typing the name of the range and then
letting XL add the sheet qualifier. This meant that although what i'd
typed was less than 256 characters, when XL added the qualifiers, the
limit was exceed. It looks like in this situation, XL converts the
function to a string and lops off the last part of the string, or
something like that.

thanks for your help.

Rob