Select range based on cell content

[tizdall]

Hello!

I am trying to find a CSE formula (not sure if I need to do this in VBA?) where I can select a range of data in a column based on fixed first cell in the column but the last point is dynamic depending on the user input.

For example:

Column A	Column B
R1
R2
R3
R4	10
R5	3
R6	0
R7	5
R8	8
R9	28
R10
R11
R12
R13

<tbody>
</tbody>

So I know that my first point will be R4 Column B but in each case the end point might be R10 in one case, R100 in another. The end result is that I want to report the minimum value in this range but I'm not sure how to specify this open ended range as a CSE formula..?

so far what I have is:

{=If(Min(B4:B9<>0|B4:B9))}

I am trying to figure out a replacement for the "B9" so that it locates the last cell from B4 that has data and then find the (non-zero) minimum in that range.


Any help would be appreciated!

 

[jai9]

Hi,

Try,

=MIN(B4:OFFSET(B4,COUNT(B4:B1000)-1,0))

Array formula. To be confirmed with CTRL + SHIFT + ENTER.

Jai

 

[tizdall]

Yeah, works great!

Thanks!

 

[tizdall]

Got an additional question:

Let's say I have the following:

Min B
Min P
Type	Value
B	170
P	150
P	151
B	181
B	100
P	101

<tbody>
</tbody>

I am using the first part as my minimum finder within the range, is it possible to attribute the minimum value of the "B's" within my data range to the Min B cell and vice versa for the P value? Essentially find the minimum of each type (B or P) depending on what the cell adjacent to it is specifying? It would only be 2 types in the adjacent cells, B or P but mixed and matched within the data range as shown in the table and in no particular order.

Again, thanks for the help before, any additional help would be appreciated!!