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.
