Cell reference in a range
 

I have a Conditional Sum that is used extensively through multipe sheets to
slice and dice a fair amount of raw data. The ending row (17424) changes each
month. The references to row 17424 is calculated in another cell ($L$3)
using the MATCH function. I would like to substitute the referenced cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the $L$3
reference?
--
Thanks,
Nick

Cell reference in a range
 

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference. MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets to
slice and dice a fair amount of raw data. The ending row (17424) changes
each
month. The references to row 17424 is calculated in another cell ($L$3)
using the MATCH function. I would like to substitute the referenced cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick

Cell reference in a range
 

Biff, thanks for responding. If I understand your question correctly, row
17424 would be the ACTUAL row reference as calculated and returned in cell L3
where the MATCH function resides to determine the last row when a new data
set is entered each month. Until now the last row has been for reference
purposes only but a new conditional sum elswhere in the sheet is giving me an
error because of the blank rows beyond the last row that has data. In the
past I just looked at a block of data (rows 1:20000) in the other formulas
but thenew conditional sum doesn't like that. There are several work arounds
(i.e., find/replace) but I need an automated soultion since I will be passing
the workbook on to others to run each month.
--
Thanks,
Nick


"Biff" wrote:

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference. MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets to
slice and dice a fair amount of raw data. The ending row (17424) changes
each
month. The references to row 17424 is calculated in another cell ($L$3)
using the MATCH function. I would like to substitute the referenced cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick

Cell reference in a range
 

Biff, I think I misunderstood your point when I responded. Yes, 17424 would
be the RELATIVE row reference. That is, the row number where the last data
element is found which is actually four rows more than the number of data
elements.
--
Thanks,
Nick


"Biff" wrote:

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference. MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets to
slice and dice a fair amount of raw data. The ending row (17424) changes
each
month. The references to row 17424 is calculated in another cell ($L$3)
using the MATCH function. I would like to substitute the referenced cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick

Cell reference in a range
 

Ok, now I'm confused!!

Try this: (normally entered, not an array like your formula)

=SUMPRODUCT(--('CDR Data'!B4:INDEX('CDR Data'!B:B,L3-3)=M39),--('CDR
Data'!O4:INDEX('CDR Data'!O:O,L3-3)=M36),'CDR Data'!E4:INDEX('CDR
Data'!E:E,L3-3))

Since your ranges start in row 4 the offset is 3 (rows 1, 2, 3). You can see
how I subtracted that from L3. If I have it backwards then just change the
the offset to +3.

Have you considered using dynamic ranges?

http://contextures.com/xlNames01.html#Dynamic

Biff

"Nick" wrote in message
...
Biff, I think I misunderstood your point when I responded. Yes, 17424
would
be the RELATIVE row reference. That is, the row number where the last
data
element is found which is actually four rows more than the number of data
elements.
--
Thanks,
Nick


"Biff" wrote:

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference.
MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets
to
slice and dice a fair amount of raw data. The ending row (17424)
changes
each
month. The references to row 17424 is calculated in another cell
($L$3)
using the MATCH function. I would like to substitute the referenced
cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now
creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick

Cell reference in a range
 

Biff,

Thanks for the feedback. I will give that a try this afternoon and will let
you know how it works. I have not tried dynamic ranges. This has been a
work in progress with me ratcheting up my Excel knowledge along the way. I
can see where dynamic ranges would help in several areas of this project and
I need to get up to speed on its use. I can't tell you how much I have
learned from this discussion group and how much I appreciate each of you that
spend so much time responding. My sincere appreciation to you all who give
so much.
--
Thanks,
Nick


"Biff" wrote:

Ok, now I'm confused!!

Try this: (normally entered, not an array like your formula)

=SUMPRODUCT(--('CDR Data'!B4:INDEX('CDR Data'!B:B,L3-3)=M39),--('CDR
Data'!O4:INDEX('CDR Data'!O:O,L3-3)=M36),'CDR Data'!E4:INDEX('CDR
Data'!E:E,L3-3))

Since your ranges start in row 4 the offset is 3 (rows 1, 2, 3). You can see
how I subtracted that from L3. If I have it backwards then just change the
the offset to +3.

Have you considered using dynamic ranges?

http://contextures.com/xlNames01.html#Dynamic

Biff

"Nick" wrote in message
...
Biff, I think I misunderstood your point when I responded. Yes, 17424
would
be the RELATIVE row reference. That is, the row number where the last
data
element is found which is actually four rows more than the number of data
elements.
--
Thanks,
Nick


"Biff" wrote:

Hi!

17424 is calculated in another cell ($L$3) using the MATCH function.

Is that the ACTUAL row reference or is that a RELATIVE row reference.
MATCH
returns a RELATIVE value unless you adjust for the offset.

Biff

"Nick" wrote in message
...
I have a Conditional Sum that is used extensively through multipe sheets
to
slice and dice a fair amount of raw data. The ending row (17424)
changes
each
month. The references to row 17424 is calculated in another cell
($L$3)
using the MATCH function. I would like to substitute the referenced
cell
($L$3) for the fixed row number in all formulas using find/replace.

My formulas look like;
=SUM(IF('CDR Data'!$B$4:$B$17424=$M39,IF('CDR
Data'!$O$4:$O$17424=$M$36,'CDR
Data'!E$4:E$17424,0),0))

I used to just extend the range to an extreme (20000) but this now
creates
an #N/A in some new formulas that I am using.

Can you show me what the replacement formula should look like using the
$L$3
reference?
--
Thanks,
Nick