If I understand your question correct
>> you have one workbook/spreadsheet where you have several sheets
>> 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
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.
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
Is there some way to do this, without programming?
For unsubscribe instructions e-mail to: firstname.lastname@example.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Impressum (Legal Info)
: 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