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


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
-- 
========================================================================
Hylton is a Lions Club member of Lions Club of Fish Hoek (District 410A)
http://www.fishhoeklionsclub.org.za being part of the worlds largest NGO
========================================================================

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

Context


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.