Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Indirect Row & cell formula
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
=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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
Can a Formula in Cell X modify Cell Y? | Excel Discussion (Misc queries) | |||
looking for a formula | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
can i colour a cell on basis of results of a formula e.g clour bl. | Excel Worksheet Functions |