Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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.999 99999999999E+307},$B:$B)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$B :$B))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$C:$C)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$C :$C))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$D:$D)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$D :$D))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$E:$E)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$E :$E))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$F:$F)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$F :$F))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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" wrote in message
oups.com...
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.999 99999999999E+307},$B:$B)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$B :$B))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$C:$C)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$C :$C))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$D:$D)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$D :$D))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$E:$E)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$E :$E))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+307},$F:$F)),0,
MATCH({"ZZZZZZZZZZZZZZZ",9.99999999999999E+307},$F :$F))),
MAX(IF(ISERROR(MATCH({"ZZZZZZZZZZZZZZZ",9.99999999 999999E+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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

You're welcome. Thanks for the feedback!

Biff

"Ken Johnson" wrote in message
ups.com...
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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Johnson
 
Posts: n/a
Default Multi-Column Dynamic Named Range...Is there an easier way?

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamic column chart - auto sort on data range jimfrog Charts and Charting in Excel 0 March 29th 06 02:45 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Array to named range conversion... i-Zapp Excel Discussion (Misc queries) 4 October 25th 05 09:09 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
dynamic named range function MJB Excel Worksheet Functions 1 August 5th 05 05:56 AM


All times are GMT +1. The time now is 07:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"