[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
UmTheMuse
General User

Joined: 05 Apr 2012
Posts: 33
Location: United States

 Posted: Fri Apr 27, 2012 12:00 pm    Post subject: [SOLVED] Non-contiguous data range Sorry, you guys are probably getting tired of my many easily-solved posts. I'm also trying to tackle this particular problem in two different ways. I saw on a different post that it was possible to use equations to define a database range (they used OFFSET(), I believe). Is it possible to use this ability to get around the limitation of a contiguous data range? More specifically, I've got a bunch of tables that are set up in the same way. Could I define each table as its own range, then define a range that is a combination of these other ranges?Last edited by UmTheMuse on Wed May 02, 2012 7:03 am; edited 1 time in total
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Fri Apr 27, 2012 1:03 pm    Post subject: Do yourself a favour and copy everything into one list._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

Posted: Fri Apr 27, 2012 5:30 pm    Post subject:

Some functions allow the use of the union operator (tilde, "~").
For example
 Code: =COUNTIF(RNG_1~RNG_2~RNG3;"b")
counts the "b"s within the union of the three named ranges RNG_1, RNG_2 and RNG_3.
To define a range that is a combination (union) of other named ranges you cannot use the union operator in the "Define Names" dialogue like say RNG_1~RNG_2~RNG_3.
You could use a formula in a cell to create the combination...
 Code: =IF(ROW(D1)-ROW(D\$1)+1<=ROWS(RNG_1);INDEX(RNG_1;ROW(D1)-ROW(D\$1)+1);IF(ROW(D1)-ROW(D\$1)+1<=ROWS(RNG_1)+ROWS(RNG_2);INDEX(RNG_2;ROW(D1)-ROW(D\$1)+1-ROWS(RNG_1));IF(ROW(D1)-ROW(D\$1)+1<=ROWS(RNG_1)+ROWS(RNG_2)+ROWS(RNG_3);INDEX(RNG_3;ROW(D1)-ROW(D\$1)+1-(ROWS(RNG_1)+ROWS(RNG_2)));"")))
in D1 filled down column D will automatically return the combination of RNG_1, RNG_2 and RNG_3.

Ken Johnson
_________________