Linked sheets

[pzh20]

I have like most people, one sheet with formula that addresses other sheets e.g. =SUMIF('Nat West'!$C$3:$C$335,"*"&TRIM('BL Blces'!B37)&"*",'Nat West'!$D$3:$D$335)+SUMIF('Credit Card'!$D$4:$D$18,"*"&TRIM('BL Blces'!B37)&"*",'Credit Card'!$E$4:$E$18)

Is there any way so the if I add an extra row say at 'Nat West'!$C$336 this sumif will get updated to reflect the new range? Some way of addressing the first and last rows of the range?

Many thanks
Pete

 

[number kruncher]

When I need to do what you are looking for, I use Dynamic Ranges.

I use OFFSET to alter the Range, using the COUNT function, eg

=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTIF(Sheet1!$A:$A,">0")-1,0)

This changes the range in the Refers to box, to suit number of entries in Column A.

Hope this helps.