Re: Change Formula to use Named Range

From: hgrove (hgrove.1akwz9_at_excelforum-nospam.com)
Date: 08/06/04 

Date: Fri, 6 Aug 2004 13:12:47 -0500

QTE wrote...
>As you can see from the quoted extract below and previously,
>my dynamic range is the same as the range in my =SUM
>Formula. Why is this EXTREMELY UNCLEAR? . . .

Possibly because you obviously don't understand that

INDIRECT("$M"&ROW()-9&":$M"&ROW())

is *NOT* necessarily the same range as given by

=OFFSET('deptanalysis'!$M$9,2,0,COUNTA('deptanalysis'$M:$M),1)

Indeed, this would only ever be the case for formulas entered in cells
in row 20 (the only time when ROW()-9 == $M$9 offset +2 rows) summing
M11:M20 (since ROW()-9 to ROW() necessarily spans 10 rows, so if it
started at row 11 it'd have to end at row 20), so there would also have
to be exactly 10 nonblank cells in all of column M.

Reread Frank Kabe's last response to one of your earlier follow-ups:
"yes you can replace this with a name but not with a name definition
like your current one. Maybe you'll try to explain whyt you're trying
to do?"

And how did you respond to Frank's question? "I would like to use a
Named Range in the Formula to make the column and formula referencing
more meaningful and transparent." Which, if it has any meaning at all,
would mean that you wanted to make the column a variable. That's the
premise under which I've been operating.

>You can see from the details given that the Dynamic Range and
>SUM Range are the same in this instance and that the latter is
>relevant to the former: if you bothered to read, before
>SPOUTING OFF IN CAPITALS.

As pointed out above, only in very specific circumstances would the two
derived ranges ever be the same. The extreme specificity of those
circumstances led me to believe (*STILL* believe) that you're unclear
how they're related. I'll put this in simple terms: do you want the
range in a column specified elsewhere that runs from 9 rows above down
to the row of the cell in which you'd use this formula, so

OFFSET(INDIRECT("RC1",0),-9,ColNumSpecifiedElsewhere-1,10,1)

or do you want the range from the column specified elsewhere beginning
two rows below what's currently row 9 extending down from there by as
many cells are are nonblank in that column, so

OFFSET($A$9,2,ColNumSpecifiedElsewhere-1,
COUNTA(OFFSET(INDIRECT("$A:$A"),0,
ColNumSpecifiedElsewhere-1)),1)

??!

Your follow-ups HAVE NOT resolved this. Perhaps in your mind you
believe they have, but you haven't exactly been showered with
responses. Someone with a scrap of intelligence might figure that
perhaps their own communication might just possibly be unclear to
others.

>If anything was EXTREMELY UNCLEAR it was your suggested re-
>working of the Formula in the Name Refers To Box. It was very
>clearly documented that it was the =SUM Formula referencing
>Column "M" that I required to be replaced with a Name.

If you believe this, then PRECISELY what was wrong with Frank Kabel's
original response, "why no use =SUM(Office)?" If you wanted to use
column M as you had used in your definition of Office, then this would
work (given the extremely specific circumstances listed above).

That seems not to have been what you wanted. Several more follow-up and
responses later, you wrote:

"The current formula already starts in M11 (taking account of the
Offset) with a height of the filled cells within M:M.

Can the column reference "M" be replaced with a Name? If so, how?

Existing Formula:
=SUM(INDIRECT("$M"&ROW()-9&":$M"&ROW()))

Name Refers To Box:
=OFFSET('deptanalysis'!$M$9,2,0,COUNTA('deptanalysis'$M:$M),1)"

That's when Frank responded as I have previously quoted in this
response. There's no obvious relationship between the OFFSET-generated
range in your definition of Office and the INDIRECT-generated range in
your SUM formula. They can refer to the same thing in one precise set
of circumstances as already listed. If that set of circumstances would
always hold, then simplicity would argue for defining Office as either

=OFFSET('deptanalysis'!$A$11,0,ColNum-1,10,1)

if you specify the column (M in your examples) using its column number,
or

=OFFSET('deptanalysis'!$A$11,0,COLUMN(INDIRECT(ColLtr)&"1"))-1,10,1)

if you specify the column using its column letter. Of course, you could
also define Office as either

=INDIRECT("'deptanalysis'!R[-9]C"&ColNum&":RC"&ColNum,0)

or

=INDIRECT("'deptanalysis'!"&ColLtr&(ROW()-9)&":"&ColLtr&ROW())

Then you could use the formula

=SUM(Office)

as Frank originally proposed.

>The reason for Rewriting yet again what I'd already written was
>in your own words "JUST TO EXPLAIN THIS DARN THING"! If
>you'd only taken the time to read the Posting.

I've now read it several times through, and it's just as opaque as it
was the first time through. At some point *YOU* need to realize that
what may be clear to you is clear to *only* you.

>Furthermore, If you do not have the temperament to assist in a
>calm and measured manner, perhaps you should not bother!

I can respond to whatever I want. If you don't like it, tough. I enjoy
trolling for idiots, so if I piss off the occasional idiot, so much the
better. I'm not yet certain whether you qualify. Your next response
should decide the matter one way or the other. 

---
Message posted from http://www.ExcelForum.com/
Loading