create a summary table from another table and add formulae

[merlin777]

I have a table with a share portfolio on one sheet which includes the stock name and book values amongst other columns. Some stocks appear more than once (intentionally). Shares come and go so the table changes regularly.

From that table's data, on another sheet I want to summarise the total book value for each share (and therefore each share must appear only once). However, to each row in the summary table I need to add 3 extra columns, one will be a number value (manually added) pertaining to that share and the others will be a mathematical formula and an IF formula.

I've thought about using a pivot table but i'm not good with those and I can't see how I would add the extra columns and get the rows to follow changes in the rows of the pivot table.

I don't mind if I have to refresh it manually.

Please could someone suggest an approach to get me started?

many thanks.

 

[merlin777]

OK, I'm part way there.

I figured out how to add columns with 'calculated field' but it isn't very friendly.

I can't work out how to add a column to the pivot table with a unique value for that stock or how to refer to that column in a calculated field formula.

What I want to end up with is:

stock name / sum of values of that stock / target value for stock / sum of values-target value / increase, decrease or hold

So, the first 2 columns are already taken care of by the pivot table, the 3rd column I need to enter a target value manually, the 4th column calculates the difference between the sum and the target and the last column will be an IF formula which returned if that stock needs to be increased, decreased or left alone.

any thoughts gratefully received....