noncontiguous range, minimal row...

[hurgadion]

Hi,
is it a simple Way to find a minimal row of a noncontiguous Range (number of Areas may be unknown... but we have function AREAS()) using a Formula ??

for (B4:B7,E2:E15,G6:G10,I3:I20) result is 2...

best regards,
hurgadion

 

[billszysz]

hmm....minimal row? Why not this way below? (CSE formula)
=MIN(ROW(MyRange:MyRange))
Colon is a key

Regards

 

[Rick Rothstein]

hmm....minimal row? Why not this way below? (CSE formula)
=MIN(ROW(MyRange:MyRange))
Colon is a key

Brilliant!

 

[Rick Rothstein]

hmm....minimal row? Why not this way below? (CSE formula)
=MIN(ROW(MyRange:MyRange))
Colon is a key

Interestingly, it appears to work for non-contiguous ranges only.... a single contiguous range seems to always return 1.

 

[Rick Rothstein]

hmm....minimal row? Why not this way below? (CSE formula)
=MIN(ROW(MyRange:MyRange))
Colon is a key

Interestingly, it appears to work for non-contiguous ranges only.... a single contiguous range seems to always return 1.

So it would seem the generalization of your method would be this...

=MIN(ROW(IF(AREAS(MyRange)=1,MyRange,MyRange:MyRange)))

 

[Rick Rothstein]

Interestingly, it appears to work for non-contiguous ranges only.... a single contiguous range seems to always return 1.

Never mind... your formula does work correctly for contiguous ranges... IF you array-enter it. Sorry for the misunderstanding on my part. Interestingly, your formula works fine normally-entered for non-contiguous ranges.

So it would seem the generalization of your method would be this...

=MIN(ROW(IF(AREAS(MyRange)=1,MyRange,MyRange:MyRange)))

I guess one would use the above only if they did not want an array-entered formula on the worksheet.

 

[Marcelo Branco]

hmm....minimal row? Why not this way below? (CSE formula)
=MIN(ROW(MyRange:MyRange))
Colon is a key

Regards

Very nice!

M.

 

[Marcelo Branco]

Hi Rick

The formula, normally-entered, worked perfectly with contiguous or non-contiguous ranges. In what situation it didn't work for you?

M.

 

[Rick Rothstein]

Hi Rick

The formula, normally-entered, worked perfectly with contiguous or non-contiguous ranges. In what situation it didn't work for you?

I'll give you the exact procedure I followed...

1) Activate Sheet2
2) Select range R6:S8
3) Click in the Name Box and enter BOB
4) Select cell P8 (no significance to that cell, it was randomly picked)
5) Enter =MIN(ROW(BOB:BOB)) into the Formula Bar and hit the Enter Key

When I do the above, the number 1 is returned by the formula. When I check in the Name Manager, it shows BOB defined as...

=Sheet2!$R$6:$S$8

Now, annoyingly, even committing the formula with CTRL+SHIFT+ENTER does not change anything... the formula still shows 1 as the result. This is confusing because in all my other tests, doing that corrected the formula's output to the correct value, but now it did nothing.

Edit Note: Could the problem be BOB:BOB is a real range (a single full column) and the Defined Name is not being looked at?

 

[Marcelo Branco]

It seems that the name BOB is the problem. I performed exactly the same steps and named the range as Range3.
The formula =MIN(ROW(Range3:Range3)), normally entered, worked perfectly.

But doesn't work with BOB - weird!

M.

 

[pgc01]

Edit Note: Could the problem be BOB:BOB is a real range (a single full column) and the Defined Name is not being looked at?

Hi

That's what I think.

I tried forcing it to consider the named range, qualifying it. If it's a workbook name like =MIN(ROW(Book1!BOB:Book1!BOB)) or if it's a worksheet name like: =MIN(ROW(Sheet2!BOB:Sheet2!BOB)).

It worked for me this way.

Anyway, better not to define a name with a column name.