Using cell value to define range

**jimbob75** · 02-15-2007, 06:49 AM

I can't find an answer after searching forums. I'm doing some analysis that uses an array formula. However, I need to change the defined range every time I add new data to the working sheet because my different datasets have different number of rows each time. The format of the formula is:

=SUM(($G$2:$G$4628>$AC$2)*($A$2:$A$4628=T2)*($H$2:$H$4628))

I have to enter the exact range every time so that I don't get the #N/A error. I have preformatted to calculate the number of cells with values, this is in cell s41. My questions...

Is there a way to define part of a range using a cell value? (e.g. something like $G$2:$G$CellValueS41)

Or, can I have Excel skip over the #N/A in the columns so it will just calculate based on cells with numbers? I'm not an advanced user so not into programming etc.

Any ideas welcome.

Thanks,

JimBob

**rylo** · 02-15-2007, 09:47 PM

Hi

Try building dynamic defined names that will react to the amount of data available. You can then use the same formula that references the names.

Insert, name, define
arng, =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)
grng, =OFFSET(arng,0,6)
hrng, =OFFSET(arng,0,7)

and your formula becomes

=SUM((grng>$AC$2)*(arng=T2)*(hrng))

Depending on the data in column A, your name definition formula may have to change....

hth

rylo

**marcospaterson** · 04-07-2007, 03:43 PM

rylo,

i've tried to add a dynamic name reference but i don't know why the reference isn't working.
do you know why would this happen?
i entered in insert>name>define> the name of the reference i wanted and in the reference field i wrote "=offset(sheets1!$C$4;0;0;COUNTA(SHEETS1!$C:$C);0)
What am i doing wrong here??

thx