I have a cell, which generates a certain range. I now want to use this
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 to
see whether it works at all:
=MAX(cell("contents",$C$1000))

but I still get the error. If I replace the cell funciton with the
actual range, it works fine, so the range is correct. Even usage of the
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,
Peter


--
p6er
------------------------------------------------------------------------
p6er's Profile: http://www.excelforum.com/member.php...o&userid=28571
View this thread: http://www.excelforum.com/showthread...hreadid=482348

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



p6er wrote:

I have a cell, which generates a certain range. I now want to use this
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 to
see whether it works at all:
=MAX(cell("contents",$C$1000))

but I still get the error. If I replace the cell funciton with the
actual range, it works fine, so the range is correct. Even usage of the
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,
Peter

--
p6er
------------------------------------------------------------------------
p6er's Profile: http://www.excelforum.com/member.php...o&userid=28571
View this thread: http://www.excelforum.com/showthread...hreadid=482348

--

Dave Peterson

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

"p6er" wrote:


I have a cell, which generates a certain range. I now want to use this
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 to
see whether it works at all:
=MAX(cell("contents",$C$1000))

but I still get the error. If I replace the cell funciton with the
actual range, it works fine, so the range is correct. Even usage of the
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,
Peter


--
p6er
------------------------------------------------------------------------
p6er's Profile: http://www.excelforum.com/member.php...o&userid=28571
View this thread: http://www.excelforum.com/showthread...hreadid=482348