Excel 2000

Can anybody correct this formula which is not working for me.

=MAX(C450:INDIRECT("plan!C"&ROW(cell("rows")))

I am trying to get the maximum value from the range C450 and the Activecell
Row in column C

thanks.

George

Hi,

Try this:

=MAX(INDIRECT("plan!C450:C"&ROW())

Regards,
KL

"George Andrews" wrote in message
...
Excel 2000

Can anybody correct this formula which is not working for me.

=MAX(C450:INDIRECT("plan!C"&ROW(cell("rows")))

I am trying to get the maximum value from the range C450 and the
Activecell
Row in column C

thanks.

George

George,

Why not just

=MAX(INDIRECT("plan!C450:C"&ROW()))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Excel 2000

Can anybody correct this formula which is not working for me.

=MAX(C450:INDIRECT("plan!C"&ROW(cell("rows")))

I am trying to get the maximum value from the range C450 and the
Activecell
Row in column C

thanks.

George

=max(Indirect("c450:c"&row(current_cell's_address) ))

"George Andrews" wrote in message
...
Excel 2000

Can anybody correct this formula which is not working for me.

=MAX(C450:INDIRECT("plan!C"&ROW(cell("rows")))

I am trying to get the maximum value from the range C450 and the
Activecell
Row in column C

thanks.

George

Bob

Your formula gives me the maximum figure in the range between C450 and the
Row where the formula is placed. Maybe I did not explain correctly but I am
wanting the result to show the maximum in the range between c450 and the
cell that the cursor is in at that moment.

so if I am in A500, then I want the maximum value in the range C450:C500

What would the formula be in this case.

Hope you can help.

Andrew


"Bob Phillips" wrote in message
...
George,

Why not just

=MAX(INDIRECT("plan!C450:C"&ROW()))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
Excel 2000

Can anybody correct this formula which is not working for me.

=MAX(C450:INDIRECT("plan!C"&ROW(cell("rows")))

I am trying to get the maximum value from the range C450 and the
Activecell
Row in column C

thanks.

George

"George Andrews" wrote in message
...
Bob

Your formula gives me the maximum figure in the range between C450 and the
Row where the formula is placed. Maybe I did not explain correctly but I
am
wanting the result to show the maximum in the range between c450 and the
cell that the cursor is in at that moment.

so if I am in A500, then I want the maximum value in the range C450:C500

That is the same thing as I have already given as far as I can see. If you
really mean A450,C500, you have a problem as the formula cell is within that
range, so you will get a circular reference.

The problem that I see with using Row() is that this gives you the row
number of the cell that the formula is in. When what I want is the row
number of where the cursor is at any particular moment.

If the formula is in D3
and the cursor is on A500 then the range I am wanting to find the maximum
value for is C450:C500

if the cursor is on A625 then the range I am wanting to find the maximum
value for is C450:C625

i.e. one part of the range is static and the other part is dynamic according
to the position of the cursor.

Can this be done.

George


"Bob Phillips" wrote in message
...


"George Andrews" wrote in message
...
Bob

Your formula gives me the maximum figure in the range between C450 and
the
Row where the formula is placed. Maybe I did not explain correctly but
I
am
wanting the result to show the maximum in the range between c450 and the
cell that the cursor is in at that moment.

so if I am in A500, then I want the maximum value in the range C450:C500

That is the same thing as I have already given as far as I can see. If you
really mean A450,C500, you have a problem as the formula cell is within
that
range, so you will get a circular reference.

Hi George,

No. I have some code that can get the cursor co-ordinates, but you cannot
use that in a worksheet formula as it uses APIs and callbacks. Every time
the cursor moves it would need to update, brrrr send shivers down my spine.

I think I can see why you want it, and I think this might be feasible-
-provide some event code that updates a SINGLE cell as you describe
-have start and stop buttons so it doesn't go on forever.

Is it worth my effort to you to have a go at that?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"George Andrews" wrote in message
...
The problem that I see with using Row() is that this gives you the row
number of the cell that the formula is in. When what I want is the row
number of where the cursor is at any particular moment.

If the formula is in D3
and the cursor is on A500 then the range I am wanting to find the maximum
value for is C450:C500

if the cursor is on A625 then the range I am wanting to find the maximum
value for is C450:C625

i.e. one part of the range is static and the other part is dynamic
according
to the position of the cursor.

Can this be done.

George


"Bob Phillips" wrote in message
...


"George Andrews" wrote in message
...
Bob

Your formula gives me the maximum figure in the range between C450 and
the
Row where the formula is placed. Maybe I did not explain correctly
but
I
am
wanting the result to show the maximum in the range between c450 and
the
cell that the cursor is in at that moment.

so if I am in A500, then I want the maximum value in the range
C450:C500

That is the same thing as I have already given as far as I can see. If
you
really mean A450,C500, you have a problem as the formula cell is within
that
range, so you will get a circular reference.