Variable range in MAX-function

[nsv]

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 (D231)

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

 

[coa01gsb]

Well the first thing I'd say is that the second part doesn't have
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

 

[Special-K]

This will work

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

unless you have blank cells in column D

More info here

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

 

[nsv]

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

Is there perhaps a VBA solution