Dynamic Ranges in Formula

[RobR1981]

Hello All,

I have the following formula: =SUMPRODUCT(--SUBTOTAL(3,OFFSET(Audit!$F$2:$F$57,ROW(Audit!$F$2:$F$57)-MIN(ROW(Audit!$F$2:$F$57)),,1)),--(LEFT(Audit!$A$2:$A$57,(FIND("-",Audit!$A$2:$A$57))-1)=F$11))

The formula does do what is intended, but I would like to use this formula in other workbooks which may have more or less than 57 rows in column F. I am trying to make the range dynamic, but am at a loss as to how to do so.

 

[mohadin]

Hi
This might help
Dynamic Named Range

• When you add a value to the range, Excel does not update the sum.
• To expand the named range automatically when you add a value to the range, execute the following the following steps. ...
• Click Edit.
• Click in the "Refers to" box and enter the formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)

(You can google "dynamic named range")
Good luck