At 12:25 24/08/2008 -0700, Mark Knecht wrote:
Imagine two data tables. One exists from $A$1 to $P$500, the second
from $AA$1 to $AP$500. Within each table there is a set of 5 columns
I need to use as a single, larger table. $F$1 to $J$500 and $AF$1 to
$AJ$500. I could copy the second table below the first making a
single large table but before I do that I was wondering if there was a way to
simply describe these two address ranges as one larger range using
some function? (I.e. - effectively $F$1 to $J$1000.)
Is there any support in Calc for creating essential a virtual
address range that grabs data from different parts of the spreadsheet?
I think what you can do here depends very greatly on what you need to
do with the virtual table that you are conceiving. Some functions,
for example, allow multiple arguments as well as ranges. So if you
need to find the largest value in your virtual column F, you could use
=MAX($F$1:$J$500;$AF$1:$AJ$500)
and you would not need to move or copy any data.
Otherwise, you might be able to do something using the INDIRECT()
function, which takes a string as its argument and interprets it as a
cell reference. For example, if you put into Z1, say,
=INDIRECT(IF(CELL("ROW";Z1)<=500;"$F$";"$AF$")&MOD(CELL("ROW";Z1)-1;500)+1)
and copy it down 1000 rows of the column, you will see column F of
the virtual table. By itself, that is no better than simple copying,
of course, but you could incorporate the INDIRECT() reference
directly in whatever formula you actually need.
But it just might be easier to move, copy, or create references to
the original data! You could create dynamic copies of your data -
using =$F$1 and so on - as a table in new columns. If you preferred,
these columns could either be hidden or be salted away on another sheet.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
