Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic column chart - auto sort on data range | Charts and Charting in Excel | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
dynamic named range function | Excel Worksheet Functions |