tweek a formula

[buz]

This formula:

=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))

is working for me till my DB goes beyond row 156.

Is there a way to 'tweek' this formula so it will keep up with my ever expanding DB?

tfyh

Buz

 

[PaddyD]

Yes - change the 'explicit' cell references to dynamic named range(s). Search here for an eg of how this is done - post back if you need more help.

Paddy

 

[PaddyD]

For example:

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)
3) Click add
4) Type Drange as another name, & put this in the refers to:
=OFFSET(Hrs!$A$2,0,0,end-1,4)

This should give you a named range that will expand with the data in cols a:d. Check this by hitting F5 & typing drange in the reference box. Add some more data & do it again - the highlighted area should grow with your data. You can now use the named range to reference your (growing) table:


=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))


becomes:

=sumproduct((index(drange,0,2)=b2)*(index(drange,0,1)=b32)*(index(drange,0,1)-c32)*(index(drange,0,4))


Paddy

 

[buz]

Paddy - thanks. I was searching the 'dynamic named range' topics and had come up a bit empty handed.

I've been practicing with a copy of my wkbk and will follow your posted example to see what i get.

Off topic - the F5 key you refer to - is that a windows keystroke? I'm using a Mac and don't believe the F5 key relates to XL.

tfyh

Buz

 

[PaddyD]

re F5 - windows (?) short cut for the go to... option found under edit | go to ..


Paddy

 

[buz]

Paddy

Did as you said - used copy and paste so as to not make errors in transcription.

when i pasted in the 'new' =sumproduct, XL alerted me to an error it said it corrected so I accepted the change. Now, one error I noticed when i copied it was a '-' where I thought an '=' belonged so i made that change.

Here's the formula as xl corrected it:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*
(INDEX(Drange,0,1)=B32)*
(INDEX(Drange,0,1)=C32)*(INDEX(Drange,0,4)))

I have yet to compare them, so will afetr I post this.

The result was a zero in my cell - something didn't work

Buz

 

[PaddyD]

buz,

If you're not making progress you can mail me the sheet & I'll add a few comments etc...check your private messages.

Paddy

 

[Dave Patton]

A slightly different way of using a dynamic range for your situation.

Per Paddy's instructions

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)

You define a name for each column that you use such as rA, rB and rD

Refers to would be similar to

=OFFSET(Hrs!$A$2,0,0,End-1,1)
Note:
Use $A$2 for rA, $B$2 for rB and $D$2 for rD

The formula would be similar to

=SUMPRODUCT((rB=B10)*(rA>=C10)*(rA<=D10)*(rD))

HTH Dave

 

[Aladin Akyurek]

On 2002-09-16 20:53, buz wrote:
Paddy

Did as you said - used copy and paste so as to not make errors in transcription.

when i pasted in the 'new' =sumproduct, XL alerted me to an error it said it corrected so I accepted the change. Now, one error I noticed when i copied it was a '-' where I thought an '=' belonged so i made that change.

Here's the formula as xl corrected it:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*
(INDEX(Drange,0,1)=B32)*
(INDEX(Drange,0,1)=C32)*(INDEX(Drange,0,4)))

I have yet to compare them, so will afetr I post this.

The result was a zero in my cell - something didn't work

Buz

Since the original formula is

=SUMPRODUCT((Hrs!$B$2:$B$156=B2)*
(Hrs!$A$2:$A$156>=B32)*
(Hrs!$A$2:$A$156<=C32)*(Hrs!$D$2:$D$156))

the new formula must be:

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*(INDEX(Drange,0,1)>=B32)*(INDEX(Drange,0,1)<=C32)*(INDEX(Drange,0,4)))

or

=SUMPRODUCT((INDEX(Drange,0,2)=B2)*(INDEX(Drange,0,1)>=B32)*(INDEX(Drange,0,1)<=C32),(INDEX(Drange,0,4)))

 

[Aladin Akyurek]

On 2002-09-16 21:38, Dave Patton wrote:

A slightly different way of using a dynamic range for your situation.

Per Paddy's instructions

1) Go to Insert | Name | Define
2) Type 'End' as the name, & put this in the refers to:
=MATCH(9.9999999E+307,Hrs!$D:$D)

You define a name for each column that you use such as rA, rB and rD

Refers to would be similar to

=OFFSET(Hrs!$A$2,0,0,End-1,1)
Note:
Use $A$2 for rA, $B$2 for rB and $D$2 for rD

The formula would be similar to

=SUMPRODUCT((rB=B10)*(rA>=C10)*(rA<=D10)*(rD))

HTH Dave

Four OFFSET formulas instead one would affect the recalc time adversely.

Aladin