Dynamic formula

P

p6er

I have a cell, which generates a certain range. I now want to use thi
range in a formula, which is also generated.

E.g.:

cell C1000 contains the text $A$1:$A$1000
cell D1000 should now either calculate MAX or MIN via a
=IF($B$1000<1234
MAX(cell("contents",$C$1000),MIN(cell("contents",$C$1000))

As I got a #VALUE error, I tried to minimize the complexity in D1000 t
see whether it works at all:
=MAX(cell("contents",$C$1000))

but I still get the error. If I replace the cell funciton with th
actual range, it works fine, so the range is correct. Even usage of th
address function within the MAX function doesn't give me a result.

I then assigned D1000 the *text* of the complete MAX function via
=CONCATENATE("=MAX(",TEXT(T3,"$A$0"),":",TEXT(U3,"$A$0"),")")

with T3 containing a 2 and U3 containing a 1000. But now I don't know
how to *execute * this command


Any ideas how to dynamically generate functions.

Thanks,
Pete
 
D

Dave Peterson

Concatenating strings will leave you with strings--not formulas that will be
evaluated.

But it kind of looks like this would work:

=if($b1000<1234,min(indirect(c1000)),max(indirect(c1000)))
 
B

B. R.Ramachandran

Hi,

=MAX(INDIRECT($C$1000)) and =MIN(INDIRECT($C$1000)

should calculate the maximum and the minimum of the range defined in the
cell C1000. So you can use this syntax in the large formula you have shown
at the beginning of your post.

Regrds,
B. R. Ramachandran
 
Top