Converting a Named Range from a Formula to an Integer Array

You could construct a string like "={1,2,3;11,12,13,...,}" and set the RefersTo to that, but the string would exceed the limits for name definition. There are workarounds that you could use, but it would be easiest to have a (very) hidden DataSheet, transfer the fixed vaules to that range

The workaround is to break the huge long string "={1, 2, 3; 11, 12, 13;...;1001, 1002, 1003}", whose length exceeds the limit for name definition, into smaller strings
Sub1 RefersTo: ="{1, 2, 3; 11, 12, 13; 21, 22"
Sub2 RefersTo: =";31, 32, 33; 41, 42, 43; 51, 5"
Sub3 RefersTo: ="2, 53; 61,62, 63; "
...
Sub20 RefersTo: ="991, 992, 993; 1001, 1002, 1003}"

Then there will be a consolidating name

Name: staticArrary RefersTo: =EVALUATE(sub1&sub2&sub3&...&sub20)

This code would help.

Please Login or Register  to view this content.