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