ExcelBanter - View Single Post

Ron Rosenfeld

On Tue, 29 Jul 2008 04:04:00 -0700, Mr. Damon <Mr.
wrote:

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$4005A 2-1)*($A$2:$A$4005<A2+1)*($E$2:$E$4005)))

Thanks

To use your formula, there is no need to look at "just" the last row, rather
you want to look at all rows from row 2 to the end.

So if you have Excel 2003 or lower, you can change 4005 to 65536.

If you have Excel 2007, change 4005 to 1,048,576

If you want to return the contents of just the last cell in column A, then one
of these **array-entered** formulas will do that.

To **array-enter** a formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

Excel 2007: =INDEX($A:$A,MATCH(2,1/($A:$A<"")))
Excel 2003 or older:
=INDEX($A$2:$A$65536,MATCH(2,1/($A$2:$A$65536<"")))

--ron