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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]R1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Min B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Min P[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]151[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]181[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD]101[/TD]
[/TR]
</tbody>[/TABLE]

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!!