Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

Most certainly you see the flexibility of that use of the 'remote ref':
you can have what ever calculations, analyzes and data on different sheets and then
collect these values to a dynamic report or for further manipulations.

On 8.5.2012 15:51, Pertti Rönnberg wrote:
If I understand your question correct
>> you have one workbook/spreadsheet where you have several sheets of data >> you want to collect specific data from some or all of these sheets to one separate sheet

I use to do that using a remote reference -- you can use that feature as well inside one worksheet as between two different sheets; and I have a faint idea that you can 'remote reference' between sheets in two different workbooks too. With the 'remote reference' I mean using (e.g.) the value "123" from cell C7 also in cell F9; then write "=C7" in cell F9.
And if you need an absolute reference write "=$C$7" in cell F9.

I use that feature in a Calc-workbook ("database") in my hopeless try to keep my family's (wife & daughter) economy in order: > a workbook with 13 sheets: 12 months named Jan, Feb, ..Dec, and the last 'Sum2012' > all worksheets Jan-Dec are equal: colA with row headings and cols B, C,.. for values/data as needed

Let us say that I want to sum the values from cell B11 in each sheet (Jan!B11, Feb!B11, ..) to the cell F11 in the last sheet "Sum2012": > in cell F11 in Sum2012 I write "=" then go to and click cell B11 in Jan, click sign "+", click cell B11 in Feb, click "+", click cell B11 in (...etc) and finally after Dec click OK (Enter). And there you are: all values in cells B11 from sheets Jan-Dec are summarized to F11 in sheet Sum2012

If you only want to 'clone' the values from one row (range) A2-D2 in Jan to range C5-F5 in Sum2012, then first 'remote ref' the cell A2 to C5 (as above: in cell C5 in Sum2012 write "=", click A2 in Jan, click Enter) and then in Sum2012 drag-copy cell C5 over range C5-F5.

If you want to 'clone' the values from a range (e.g. A2..D12 in Feb) to a range (B4-E14) in Dec, then
>    first 'remote ref' the cell A2 (as above) to (e.g.)  B4 in Dec
>    then drag-copy the cell B4 over range B4-E4
>    then mark the row-range B4-E4 and drag-copy it downwards to E14

The absolute reference ($-signs) can be used here too.

I hope I could describe the proceeding - and that it is to help.
Best regards
Pertti Rönnberg

8.5.2012 8:01, Kelly Holman wrote:
I need to take data rows from a bunch of sheets, and combine them all
on one sheet at the end, so I can do calculations/analysis of all the
data. Every way I've tried, doesn't work. I can't make a named range
that goes across multiple sheets, to use for a database or a Pivot
Table. I can use Consolidate, but it doesn't seem able to update
dynamically. Also it includes all the columns in between the ones I'm
interested in, and adds them up even though it doesn't make sense for
those columns.

Is there some way to do this, without programming?

For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.