I need to use the largest figure in a part of column D. The part is
defined with the lowest value of the range always being D2 and the
highest being determined by the number of used cells in column B, eg.
if column B contains 32 used cells then the range is (D2:D31)

I tried this formula:
=MAX(D2:ADDRESS(COUNT(B:B)-1;4;4))+1
but I cannot enter it. I get a message that it contains an error, just
as if I've forgotten a parenthesis or something like that.

I have tested the formula =ADDRESS(COUNT(B:B)-1;4;4) in spare cell in
the sheet where it works fine and writes D31 when there are 32 used
cells in column B.
Why doesn't it work in the MAX formual?

NSV


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563195

Well the first thing I'd say is that the second part doesn't have a
column reference:

=MAX(D2: 'need some reference to D here' ADDRESS(COUNT(B:B)-1;4;4))+1

Not quite sure how you do that though.


--
coa01gsb
------------------------------------------------------------------------
coa01gsb's Profile: http://www.excelforum.com/member.php...o&userid=31214
View this thread: http://www.excelforum.com/showthread...hreadid=563195

This will work

=MAX(INDIRECT(CONCATENATE("D1:D",COUNTA(B1:B1000)) ))

unless you have blank cells in column D

More info here

http://www.grbps.com/Excel7.pdf


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=563195

I do have blank cells in D and it does not work.

Is there perhaps a VBA solution?


--
nsv
------------------------------------------------------------------------
nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500
View this thread: http://www.excelforum.com/showthread...hreadid=563195