Lastrow

  • Thread starter Thread starter Mr. Damon
  • Start date Start date
M

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
 
How about something like..
=MAX(SUMPRODUCT(--($B$2:$B$65000=B2),--($A$2:$A$65000>A2-1),--($A$2:$A$65000<A2+1),--($E$2:$E$65000)))
???
 
On Tue, 29 Jul 2008 04:04:00 -0700, Mr. Damon <Mr.
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
Click to expand...

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
 
Maybe something like this:
=MATCH(LOOKUP(99^99,1:1),1:1)
=MATCH(LOOKUP(99^99,A:A),A:A)

Both are CSE functions; entered with ctrl+shift+enter

Perhaps....
=INDEX(J1:J1001,SUMPRODUCT(MAX((ROW(J1:J1001)*(J1:J1001<>"")))))


HTH,
Ryan---
 
Back
Top