OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Non-contiguous data range

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
UmTheMuse
General User
General User


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

PostPosted: Fri Apr 27, 2012 12:00 pm    Post subject: [SOLVED] Non-contiguous data range Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 27, 2012 1:03 pm    Post subject: Reply with quote

Do yourself a favour and copy everything into one list.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Fri Apr 27, 2012 5:30 pm    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group