Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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))

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there a way to determine which cell the function =min() used? Justin Excel Worksheet Functions 6 October 16th 05 01:14 PM
Offset Function works in cell, not in named range DragonslayerApps Excel Worksheet Functions 0 July 25th 05 04:39 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 5 December 3rd 04 01:06 PM
which formula or function searches for a value in a range of cell. Roccobarocco Excel Worksheet Functions 7 December 3rd 04 10:06 AM


All times are GMT +1. The time now is 04:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"