indirect application to formula
 

=SUMIF(CLIENT!I2:I378,"CHAMPION TITLE",CLIENT!Q2:Q378)

I have multiple formulas where the final number "378" changes daily in my
spreadsheet

what is the best way to update all the formulas on my calculations page
I would like to possibly link to a number on a the sheet so that it
automatically applies to all necessary areas

The sheet that i am drawing data from is "CLIENT" and the sheet i have
formulas on is called "calculations"

If your sheet set-up allows it, you could simply
use entire col references (I:I, Q:Q, etc) in the SUMIF, viz.:

=SUMIF(Client!I:I,"CHAMPION TITLE",Client!Q:Q)

If it doesn't, then one way ..

Let's assume B1 will contain the final number: 378 (say)
then you could put in say, B2:

=SUMIF(INDIRECT("Client!I2:I"&B1),"CHAMPION
TITLE",INDIRECT("Client!Q2:Q"&B1))

Just adjust the number in B1 to
whatever the final number should be

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"floridasurfn" wrote in message
...
=SUMIF(CLIENT!I2:I378,"CHAMPION TITLE",CLIENT!Q2:Q378)

I have multiple formulas where the final number "378" changes daily in
my
spreadsheet

what is the best way to update all the formulas on my calculations page
I would like to possibly link to a number on a the sheet so that it
automatically applies to all necessary areas

The sheet that i am drawing data from is "CLIENT" and the sheet i have
formulas on is called "calculations"

Hi!

Use dynamic ranges. Instructions can be found he

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

Biff

-----Original Message-----
=SUMIF(CLIENT!I2:I378,"CHAMPION TITLE",CLIENT!Q2:Q378)

I have multiple formulas where the final number "378"
changes daily in my
spreadsheet

what is the best way to update all the formulas on my
calculations page
I would like to possibly link to a number on a the sheet
so that it
automatically applies to all necessary areas

The sheet that i am drawing data from is "CLIENT" and
the sheet i have
formulas on is called "calculations"
.

surf,

If this is just the varying last row of data, you can get away with running
the SUMIF function all the way down:

=SUMIF(CLIENT!I2:I65536,"CHAMPION TITLE",CLIENT!Q:Q)
=SUMIF(CLIENT!I:I,"CHAMPION TITLE",CLIENT!Q:Q)

Or you can put the bottom row extent into a cell, like A1:
=SUMIF(INDIRECT("CLIENT!I2:I" & A1),"CHAMPION TITLE", Q:Q).

I think you can pretty much run the third parameter up and down the column.
It only includes those in the range of the first parameter. Put the ending
row number in F1, for this example.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"floridasurfn" wrote in message
...
=SUMIF(CLIENT!I2:I378,"CHAMPION TITLE",CLIENT!Q2:Q378)

I have multiple formulas where the final number "378" changes daily in my
spreadsheet

what is the best way to update all the formulas on my calculations page
I would like to possibly link to a number on a the sheet so that it
automatically applies to all necessary areas

The sheet that i am drawing data from is "CLIENT" and the sheet i have
formulas on is called "calculations"

i appreciate the info this helped me out

"floridasurfn" wrote:

=SUMIF(CLIENT!I2:I378,"CHAMPION TITLE",CLIENT!Q2:Q378)

I have multiple formulas where the final number "378" changes daily in my
spreadsheet

what is the best way to update all the formulas on my calculations page
I would like to possibly link to a number on a the sheet so that it
automatically applies to all necessary areas

The sheet that i am drawing data from is "CLIENT" and the sheet i have
formulas on is called "calculations"

I'm sure those who responded would be pleased to hear that, albeit your
reply was a mite late <g..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"floridasurfn" wrote in message
...
i appreciate the info this helped me out