Keeping # of Cells constant in a formula
 

Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't have
to change the formula coordinates every day? I've tried the $ many different
ways, but it's not working...I've viewed Help, but I'm still not having any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.
--
jeannie v

Keeping # of Cells constant in a formula
 

How can I make the H2:H12000
and the I2:I12000 remain the same every time

=SUMPRODUCT(--(REPORT!$H$2:$H$12000="Dummy"),--(REPORT!$I$2:$I$120003))


Gord Dibben MS Excel MVP


On Sun, 17 Feb 2008 11:14:00 -0800, jeannie v
wrote:

Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't have
to change the formula coordinates every day? I've tried the $ many different
ways, but it's not working...I've viewed Help, but I'm still not having any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.

Keeping # of Cells constant in a formula
 

Sounds like you want to use dynamic ranges:

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

--
Biff
Microsoft Excel MVP


"jeannie v" wrote in message
...
Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day
from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater
than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't
have
to change the formula coordinates every day? I've tried the $ many
different
ways, but it's not working...I've viewed Help, but I'm still not having
any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.
--
jeannie v

Keeping # of Cells constant in a formula
 

Hi Gord: Thank you so much! I appreciate it!
--
jeannie v


"Gord Dibben" wrote:

How can I make the H2:H12000
and the I2:I12000 remain the same every time

=SUMPRODUCT(--(REPORT!$H$2:$H$12000="Dummy"),--(REPORT!$I$2:$I$120003))


Gord Dibben MS Excel MVP


On Sun, 17 Feb 2008 11:14:00 -0800, jeannie v
wrote:

Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't have
to change the formula coordinates every day? I've tried the $ many different
ways, but it's not working...I've viewed Help, but I'm still not having any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.

Keeping # of Cells constant in a formula
 

Hi Biff: I will definitely look into dynamics as soon as possible. Thank
you for the info.
--
jeannie v


"T. Valko" wrote:

Sounds like you want to use dynamic ranges:

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

--
Biff
Microsoft Excel MVP


"jeannie v" wrote in message
...
Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day
from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater
than 3
on another worksheet in the same Workbook.....How can I make the H2:H12000
and the I2:I12000 remain the same every time I pull this data so I don't
have
to change the formula coordinates every day? I've tried the $ many
different
ways, but it's not working...I've viewed Help, but I'm still not having
any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.
--
jeannie v

Keeping # of Cells constant in a formula
 

I may have misunderstood what you were wanting to do!


--
Biff
Microsoft Excel MVP


"jeannie v" wrote in message
...
Hi Biff: I will definitely look into dynamics as soon as possible. Thank
you for the info.
--
jeannie v


"T. Valko" wrote:

Sounds like you want to use dynamic ranges:

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

--
Biff
Microsoft Excel MVP


"jeannie v" wrote in message
...
Hi Experts:

This is my formula:

=SUMPRODUCT(--('REPORT'!H2:H12000="Dummy"),--('REPORT'!I2:I120003))

I want this formula to add the data for "Dummy" that changes every day
from
H2 to H12000 on the "REPORT" worksheet and plug the # if it is greater
than 3
on another worksheet in the same Workbook.....How can I make the
H2:H12000
and the I2:I12000 remain the same every time I pull this data so I
don't
have
to change the formula coordinates every day? I've tried the $ many
different
ways, but it's not working...I've viewed Help, but I'm still not having
any
luck. I know it should be simple, but I need help.

Any help you can provide would be appreciated. Thank you.
--
jeannie v

Keeping # of Cells constant in a formula
 

After re-reading, not so sure if that's what you were asking for.

Might be Biff is more on track but.............?


Gord

On Sun, 17 Feb 2008 11:32:00 -0800, jeannie v
wrote:

Hi Gord: Thank you so much! I appreciate it!

Keeping # of Cells constant in a formula
 

OR ... maybe trying to work with inserting/deleting rows:

=SUMPRODUCT(--(INDIRECT("$H$2:$H$12000")="Dummy"),--(INDIRECT("$I$2:$I$12000
")3))

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...

After re-reading, not so sure if that's what you were asking for.

Might be Biff is more on track but.............?


Gord

On Sun, 17 Feb 2008 11:32:00 -0800, jeannie v
wrote:

Hi Gord: Thank you so much! I appreciate it!

Keeping # of Cells constant in a formula
 

Of course, we don't need the absolutes:

=SUMPRODUCT(--(INDIRECT("H2:H12000")="Dummy"),--(INDIRECT("I2:I12000")3))
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ragdyer" wrote in message
...
OR ... maybe trying to work with inserting/deleting rows:


=SUMPRODUCT(--(INDIRECT("$H$2:$H$12000")="Dummy"),--(INDIRECT("$I$2:$I$12000
")3))

--
Regards,

RD

--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...

After re-reading, not so sure if that's what you were asking for.

Might be Biff is more on track but.............?


Gord

On Sun, 17 Feb 2008 11:32:00 -0800, jeannie v
wrote:

Hi Gord: Thank you so much! I appreciate it!

Keeping # of Cells constant in a formula
 

Hi Gord:

Your solution worked perfectly and I used it for 2 applications...Thank you
so much for your expertise.

jeannie v


"Gord Dibben" wrote:


After re-reading, not so sure if that's what you were asking for.

Might be Biff is more on track but.............?


Gord

On Sun, 17 Feb 2008 11:32:00 -0800, jeannie v
wrote:

Hi Gord: Thank you so much! I appreciate it!