Multi-Column Dynamic Named Range...Is there an easier way?

[Ken Johnson]

I'm trying to set up a 6 column dynamic named range where all of the
columns could possibly have text or number (ie Number Format = General)
as well as any number of blanks.

To locate the bottom-most row with data I have adapted a formula from
Bob Phillip's xldynamic website that uses MATCH and MAX with
"ZZZZZZZZZZZZZZZ" and 9.99999999999999E+307.
To get the formula to work on my old iMac I've had to include ISERROR.
The final formula looks like...

=MAX(MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$B:$B)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$B:$B))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$C:$C)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$C:$C))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$D:$D)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$D:$D))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$E:$E)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$E:$E))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$F:$F)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$F:$F))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$A:$A)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$A:$A))))

(the column addresses are out of order, but that doesn't matter, it
still works)

This has too many characters (785) to go into the Refers to: box on the
Define Names dialog.
I think the limit is 255 characters.

I tried reducing the characters using "ZZZZZZ" and 9.9E+307 but it's
still too big with 520 characters.

Also, this formula is only the Height argument of the OFFSET formula in
the Refers to: box.

To overcome this problem I have entered the above formula into G1 and
the formula ="$A$2:$F$" & G1
into G2.

I will then hide column G.

Then the formula in the Refers to: box is...

=INDIRECT(Sheet1!$G$2)

This works, but I can't help feeling there is any easier way.

Any ideas?

Ken Johnson

 

[Biff]

Hi!

Do you really need to reference the entire column?

Array entered:** (can't use entire columns)

=MAX((A1:F65535<>"")*(ROW(1:65535)))

** as a worksheet formula. If you use this as an argument in a named range
formula it should automatically be processed as an array. Also, the larger
the range the slower this formula is to calculate.

=OFFSET(A1:F1,,,MAX((A1:F65535<>"")*(ROW(1:65535))))

Biff

 

[Ken Johnson]

Hi Biff,
Thanks for that I'll try it out on a reasonable sized range to see how
fast it is compared with my monster, which is quite slow on my old
iMac.
Also, thanks for clearing up the bit about using an array formula in
the Refers to: box, that's something I didn't know (one of the many
things I don't know:-\).

Ken Johnson

 

[Ken Johnson]

Hi Biff,
Truly amazing!
Tried it out using 1000 rows with no detectable delay after hitting
Enter, even on my slow machine.
Thanks heaps.
Ken Johnson

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

 

[Harlan Grove]

Biff wrote...

Do you really need to reference the entire column?

Array entered:** (can't use entire columns)

=MAX((A1:F65535<>"")*(ROW(1:65535)))

....

That's not an exact replacement for the OP's original formula. The OP's
formula would treat "" as a possible valid text string. The formula
above wouldn't. Exact replacement requires

=MAX((1-ISBLANK(A1:F65535))*ROW(A1:F65535))

 

[Ken Johnson]

Hi Harlan,

Thanks for raising that very esoteric point.

Consideration of data in the form of an empty string was not part of my
original thinking, even though my original formula had them covered.

Ken Johnson