Indirect Row & cell formula

G

George Andrews

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
 
B

Bob Phillips

George,

Why not just

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

--

HTH

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

George Andrews

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
 
B

Bob Phillips

George Andrews said:
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
Click to expand...

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.
 
G

George Andrews

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
 
B

Bob Phillips

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