Cell reference in a range

[Nick]

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?

 

[Biff]

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]

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

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]

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

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

 

[Biff]

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

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.

 

[Nick]

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.