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

 

[Rick Rothstein]

That's what I think.

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

It worked for me this way.

Yep, qualifying the Defined Name fixed the problem.

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

Agreed, but if we are going to put this out there as a general formula, we are going to have to include that qualification as well. By the way, another proof that one should not use column names for Defined Names... make a non-contiguous selection and name it both PGC and RICK... entering PGC:PGC into the Name Box selects Column PGC, but entering RICK:RICK into the Name Box select the rectangular range that encompasses the Areas making up the non-contiguous range. So maybe the qualification I mentioned at the beginning is not really necessary as using a column name as a Defined Name is generally a bad thing to do (although really easy to forget).

 

[GTO]

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

Regards

Kudos!