HELP Lastrow Range

[Mr. Damon]

HELP!!! I'm doing a formula and I'm trying to get it to look at the last row
in column B every time I paste information in the worksheet I have included
my formula. All I see is the VB But I need it in formula. I need instead of
going to 4005 I need it to go to the end of the last row.

=MAX(SUMPRODUCT(($B$2:$B$4005=B2)*($A$2:$A$4005>A2-1)*($A$2:$A$4005<A2+1)*($E$2:$E$4005)))

Thanks

 

[Bernie Deitrick]

1) Your formula will work fine as long as you do not extend beyond row 4005 - if you already are,
then simply increase the 4005s to numbers larger than you expect to need.

OR

2) Before you paste data into your table, insert sufficient rows within the referenced area, then
copy and paste the data within the inserted rows. Excel will update your formula automatically.

HTH,
Bernie
MS Excel MVP