Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filtering out info (xp)

    Ok I have a chart with 2 columns of information I want to extract and look at
    worksheet with information look like

    supplier_a 5
    supplier_b 10
    Supplier_a 6
    supplier_c 23
    supplier_a 44
    supplier_a 23
    supplier_d 6

    what I want to do is have a second worksheet where

    supplier_a (total from 1st worksheet)
    supplier_b....
    supplier_c...

    but I want this page to update when the next record is added to the first page wither it be a new supplier or and old supplier having more added.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering out info (xp)

    Hans,
    You will get no argument here from me. The only problem is that my boss is a spread sheet fan and thinks that everything should be in one. including this mess and will not let me change it over to Access.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filtering out info (xp)

    It's not difficult to create formulas that total the entries per supplier: say a supplier name is in A1 on the second sheet. Place the following formula in B1:
    <code>
    =SUMIF(Sheet1!A:A,A1,Sheet1!B:[img]/forums/images/smilies/cool.gif[/img]
    </code>
    where Sheet1 is the name of the first sheet.
    But updating the list of unique suppliers in the second sheet automatically is more work. See here for the kind of monster formulas you need.

    This kind of thing is *much* easier in Access.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering out info (xp)

    I have been hitting my head off of the wall over this formula. How do I use this....

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filtering out info (xp)

    I find the formulas far too complicated to work with. I have attached an alternative: a pivot table that is updated automatically by a worksheet event procedure. Make sure that macro security is not set to high, and enable macros when you open the workbook. The code is in the Sheet1 worksheet module.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •