indirect application to formula

[floridasurfn]

=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"

 

[Max]

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

 

[Biff]

Hi!

Use dynamic ranges. Instructions can be found here:

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

Biff

 

[Earl Kiosterud]

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.

 

[floridasurfn]

i appreciate the info this helped me out

 

[Max]

I'm sure those who responded would be pleased to hear that, albeit your
reply was a mite late <g>..