help needed with pivot table

Topic

New Reply

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

Reply | Quote

Replies

sorry, this is the proper attachment

Reply | Quote

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

Reply | Quote

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/showthread//161456-Need-to-create-summary-of-data-in-two-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

Reply | Quote