Re: [libreoffice-users] Combining data rows on one sheet

Kelly,
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: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
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