On Sun, Aug 24, 2008 at 2:55 PM, Brian Barker <[EMAIL PROTECTED]> wrote:
> 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
Brian,
Thanks. It does help even if it's telling me that I cannot do what
I want to do without copying the data. I think I'll just make the
spreadsheet larger, copy the data and move forward.
Thanks,
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
