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

Kelly,
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.
Pertti


On 8.5.2012 15:51, Pertti Rönnberg wrote:
> 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