[libreoffice-users] Re: Calc row filter extraction

Hi Hylton,

If I understand well, you would like to see some "reports" of expenses, 
by different categories or person...

Maybe a quick "Pilot Table" (I'm not sure of the name of this feature in 
English...) could do the job, no ?

You can try, the feature is in the "Data" menu, "Pilot Table" - Create
Place your column D as the filter, then Col C on the line, col B on the 
colum, and E in the table. It should propose "Sum" as default function. 
Then create, you will see the result (a table with all possible sums 
accessible).

The only thing you miss there is the extraction per person (you sheets B 
and C of your example...)

Hope this helps,

Kind regards from Olivier

Le 3/08/2011 18:27, Hylton Conacher (ZR1HPC) a écrit :
> On 28/07/11 23:53, planas wrote:
> > Hi Hylton,
> >
> > On Thu, 2011-07-28 at 21:50 +0200, Hylton Conacher (ZR1HPC) wrote:
> >
> > > Hi all,
> > >
> > > I have a 'data' spreadsheet sheet for my family medical aid on LO 3.3.1
> > > with multiple columns and different information in each column. Please
> > > see the text example I include below.
> > >
> > > What I would ideally like to do is have rows on sheet A, where Column D
> > > is the same, linked onto another sheet(B). My next requirement is to
> > > group all the like column B on sheet B, C and link them onto sheet D but
> > > group them according to column B and SUM the amounts from sheets B, C.
> > >
> > > I have included a brief text example below:
> Re-pasted
> Sheet A
> A       B       C       D       E
> 20-July SVP     SBP     Robert  200
> 20-July STP     SPG     Hazel   100
> 21-July STP     SBP     Robert  180
> 22-July SVP     SBP     Robert  50
> 23-July STP     SBP     Hazel   400
>
> Sheet B (All 'Robert' entries)
> A       B       C       D       E
> 20-July SVP     SBP     Robert  200
> 21-July STP     SBP     Robert  180
> 22-July SVP     SBP     Robert  50
>
> Sheet C (All 'Hazel' entries)
> A       B       C       D       E
> 20-July STP     SPG     Hazel   100
> 23-July STP     SBP     Hazel   400
>
> Sheet D (All 'SBP' entries)
> A       B       C       D       E
>         STP     SBP     Robert  100
>         STP     SBP     Hazel   500
>         SVP     SBP     Robert  250
>
> > > I have done some googling regarding row extraction and have also
> > > investigated the SUMIF command. and whilst I can get a total of all the
> > > STP's, I cannot SUM it by user at STP.
> > > The SUMD command has also raised its head but I cannot figure out how to
> > > get it to work in conjunction with SUMIF.
> > >
> > > Any help appreciated, even telling me MySQL is a better option. My
> > > problem is that the data is Sheet A is ever growing and being edited by
> > > a basic Excel user, and sheets b->D need to reflect those changes made
> > > on sheet A.
> > >
> > > Again, Any Help Appreciated
>
> > If I understand your problem, you want conditionally add data based on a
> > selection criteria from different sheets. I believe sumif works best
> > using columns from on one sheet.
>
> Almost. Only Sheet A is user entered, all the others are retrieved from
> Sheet A.
>
> > I wonder if using an intermediate sheet using vlookup and sum these
> > results might work. One issue, vlookup is to have a unique lookup
> > parameter in the selection column.
>
> I do not understand the case of using an intermediate sheet as each
> sheet(B,C,D) only requires population from certain rows on Sheet A.
>
> I have also tried for the last few days to get vlookup working but have
> failed with that too, despite reading the help and using google.
>
> Using the above tables, could you give me an example of the formula to
> copy only certain rows onto anther sheet?
>
> Appreciated
> Hylton

--
---------------
Olivier Bietzer


--
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