Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Smile help needed with pivot table

    hello everyone,
    I need a little help with a pivot table. I’m running XL 2003, a bit old but does the jobs I need.
    will refer to attached spreadsheet:
    sheet “all data” shows symbols, prices and other parameters of preferred shares.
    from that, I got pivot tables shown in sheet “sorted” (pls notice rows 3 & 4 hidden).
    table 3 is my problem: what’s shown in sheet “what I want to get” depicts my target:
    a) sorting by decreasing current yield, and
    b) conditionally formatted if larger or smaller than nominal yield.
    furthermore; I have difficulty to create a pivot with THREE columns, and need some guidance.
    TIA.

    danielr2
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    re: help neede with pivot table

    sorry, this is the proper attachment
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Create the pivot table
    Row labels are:
    Symbol and NY
    Values are
    Sum of CY

    Sort the symbol by CY
    and sort NY largest to smallest

    Add the conditional format

    I don't understand why you need a pivot table for this. You can just sort the data directly and hide the unneeded columns....

    Steve

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    danielr2 (2014-04-26)

  5. #4
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi sdckapr;
    thank you very very much for your reply.
    it's been extremely helpful to guide me into the solution of my problem. by playing a bit, i achieved what i am attaching in "version 2" (hidden all columns and whatever is not relevant to explain the point).
    i want to have DY sorted down and conditionally formatted if CY < NY.
    why a pivot table?
    few reasons:
    1) the prices ("Last Price" in worksheet all data), change according to market conditions, so does CY (please notice the formula in this version), a couple of mouse clicks Refreshes the table, keeping the conditional formatting.
    2) i have a similar file where the (ascending / descending) sorting required much effort and more complex formulas (many of them learned here).
    pls see my second attachment how i have it so far.xls, (also with irrelevant cells hidden), still not being able to conditionally format CY < NY.
    3) i am an assiduous reader of this forum.
    you, the extraordinary an knowledgeable people who assist us has taught me a lot.
    however, i don't know to program VBA, and even copying and pasting a macro is prone to errors and / or difficulties of adaptation.
    in recent posting:

    http://windowssecrets.com/forums/sho...o-column-table

    many posts offered solutions through VBA and macros, until a pilot table offered another approach more accessible for me to try in the future.
    that's why i tried to get how i have it so far.xls "reworked" using a pivot table.
    i believe that (at least for me, given my limitations), the pivot table offers awesome possibilities.

    thanks AGAIN.


    danielr2
    Attached Files Attached Files

Posting Permissions

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