Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Ok - heres what we have:
K1 = 7 K8 = 8 Q9 = 5 Cell where function is to go is K9. What I am trying to do is look at the range from directly above cell (i.e. K8) to the cell in column K with the row the value of Q9 (i.e. K8:K5 in this case). The value in Q9 changes and I need to calculate the value of this cell as the Max value from this range plus 1. The value in this example should equal 9. I have tried several variations of the following: =MAX(K8:K&Q9)+1 =MAX(K8:"K"&Q9)+1 =MAX(K8:"K"&INDIRECT(Q9))+1 =MAX(K8:"K"&LOOKUP(Q9))+1 All with no success. Any and all help appreciated. Geoff. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Hi!
Try this: =MAX(INDIRECT("K"&K8&":K"&Q9))+1 Which evaluates to: =MAX(K5:K8)+1 Biff wrote in message oups.com... Ok - heres what we have: K1 = 7 K8 = 8 Q9 = 5 Cell where function is to go is K9. What I am trying to do is look at the range from directly above cell (i.e. K8) to the cell in column K with the row the value of Q9 (i.e. K8:K5 in this case). The value in Q9 changes and I need to calculate the value of this cell as the Max value from this range plus 1. The value in this example should equal 9. I have tried several variations of the following: =MAX(K8:K&Q9)+1 =MAX(K8:"K"&Q9)+1 =MAX(K8:"K"&INDIRECT(Q9))+1 =MAX(K8:"K"&LOOKUP(Q9))+1 All with no success. Any and all help appreciated. Geoff. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Thanks Bill, but I actually want the range to be from the cell directly
above, to the row indicated by the value in the Q column. For example if Q21=5 and Q30=22, then K21 range needs to be from K20:K5 and K30 range needs to be from K29:K22. I can't seem to get this to work. Regards, Geoff. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Just to clarify - I want to copy this cell to all cells in this column
so the formula will always recalculate to detemine the range to be from the cell directly above to the row indicated by the value in the Q column. Geoff. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
This is what I would have thought was correct (cell is K9) but I just
keep on getting an "Invalid Cell Reference" error. =MAX(INDIRECT(K8&":K"&Q9)) When I Evaluate the formula it equates to: =MAX(INDIRECT(";K5)) Seems strange that it contains a colon. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Ok, now I'm confused!
Biff wrote in message ups.com... This is what I would have thought was correct (cell is K9) but I just keep on getting an "Invalid Cell Reference" error. =MAX(INDIRECT(K8&":K"&Q9)) When I Evaluate the formula it equates to: =MAX(INDIRECT(";K5)) Seems strange that it contains a colon. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Got it!!!
=IF(MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9))+1=0,"",MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Biff, not Bill.
(I'm here to help <vbg.) wrote: Thanks Bill, but I actually want the range to be from the cell directly above, to the row indicated by the value in the Q column. For example if Q21=5 and Q30=22, then K21 range needs to be from K20:K5 and K30 range needs to be from K29:K22. I can't seem to get this to work. Regards, Geoff. -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value of cell to determine range in MAX Function
Good deal!
Biff wrote in message oups.com... Got it!!! =IF(MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9))+1=0,"",MAX(INDIRECT("K"&(ROW()-1)&":K"&$Q9)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a way to determine which cell the function =min() used? | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
which formula or function searches for a value in a range of cell. | Excel Worksheet Functions | |||
which formula or function searches for a value in a range of cell. | Excel Worksheet Functions |