Help with total rows...

[Risels7]

Hi,

I have received some excellent help from this site, and have now finished the work I needed to do.
I'm just wondering if i could make life easier when updating my database.

I currently have 14,654 rows and will be adding about 200 or so each month next year..
I am using 12 formulas which pulls data from all rows, but am wondering if instead of say A2:A14654,
I could instead say A2: the bottom row. (I use A1 for titles) is there a way to do something like this?

also, My formulas vary in format Eg..

=SUMPRODUCT(($J$2:$J$14654=$AB2)*(V2:V14654=2),--(W2:W14654=0))

=MAX(IF(J2:J14654=AB2,IF(Y2:Y14654>1.9,IF(M2:M14654<=100,D2:D14654))))

=SUMPRODUCT(--(V2:V14654=2),--(W2:W14654=1)) etc..

some have $ signs, and some are of different formats... will this have an effect when i add more rows?

I just want to make as easy as possible to update my database, and not have to worry if my results are accurate or not.

Thanks all..

[Richard Buttrey]

Hi,

You should get into the habit of using dynamic range names. These automatically adjust for the length/width of data lists, and using a named range instead of cell refs. also makes formulae easier to read.

I can offer no better advice than to visit Debra's web site http://blog.contextures.com/archives...automatically/

[Andrew-R]

If all of the rows are going to be non-blank then you might get away with something like:

=SUMPRODUCT(($J$2:OFFSET($J$2,COUNTA(J:J)-1,0)=$AB2)*(V2:OFFSET($V$2,COUNTA(J:J)-1,0)=2),--(W2:OFFSET($W$2,COUNTA(J:J)-1,0)=0))

Note that only one of your columns always needs to have data in for this to work.

[Risels7]

ok.. this is exactly what i'm looking for...just looks a little more complicated then I excpected.

Ill have a play around it... is there a specific part of the code I can just copy and paste it into my formulas with :14654 in, and just edit according to my formula requirements? or will it depend on the formula i'm editing.

Thanks guys

[Andrew-R]

Richard's suggestion is much better - dynamic ranges would be the way to go and less work.

[Risels7]

Ok cool... so for these dynamic ranges...do i apply this to the data, or to the cells i have beside the data with the formulas and the calucations.

A-Z has Data in, and AB and beyond has formulas with cells for the results to appear in... like calcualtors.

[Risels7]

Ok, I have now named all my ranges... just having trouble adjusting my formulas now...

=SUMPRODUCT(($J$2:$J$14654=$AB2)*(V2:V14654=2),--(W2:W14654<2))

J = WName, V = 1S and W = 2S

if somebody can adjust that code to work with the name ranges, i can figure out how to edit these myself...

Many thanks..

[oeldere]

You can better show us the file, without confidentional information.

Then we can have a better look on your problem.

[Risels7]

I'd rather not if i can help it...

is it not possible for you to edit that formula without it?

thanks

[Andrew-R]

Should just be:

=SUMPRODUCT((WName=$AB2)*(1S=2),--(2S<2))

[Risels7]

Thank you...

Alot simpler then I initially thought,

Thanks guys !