| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1849
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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
