• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Preserve Calculated Fields Upon Sort of Master Table

fixthis

fixthis

New Member
I have developed a portfolio tracking workbook consisting of the following worksheets:

Master
Dividend Per Share
Dividends Received

See attached file.

Note that the Master holds all of the pertinent investment data.
The Dividend Per Share and the Dividends Received worksheets have specific dividend data which was inputted based on the original arranged rows from the Master table. The Master table is arranged alphabetically by stock symbol (Col B). Problem arises when I want to sort any of the columns in the Master table, the calculated data in the date columns of the two Dividend worksheets no longer match the original information. In other words the relationship between a specific stock or fund is broken. Is there a way to preserve this? Pivot tables and VBA are not my strong points, but willing to redesign my tracking system if need be.

Thank you.
 

Attachments

fixthis
As long as there are formula based and fixed values in same row ... those Your sheet won't work...
eg Dividends Received-sheets cell B5-value can be any of Master-sheets B-column value ... means ...
eg if sort Master-sheet per symbol Ascending or Descending Symbol CC's Sep 20-value can be $5.00 or $0.89 ... really?
... You should make 'minor' ( = a lot of ) modifications to get that work.
 
I guess I am the wrong person to help because I loath traditional spreadsheet practice :(.
An example of the changes that might make your analysis robust to sorting can be illustrated with the 'Div Counter' calculated field. Instead of using cell position and taking the maximum value above the formula, you could base the formula on cell content
= IF(Dividend?="Div", COUNTIFS(uniqueCode,"<="&uniqueCode, Dividend?, "Div"), "-" )
 
vletm: I did not realize that Excel is limited on what you can do with the data when rows have both fixed and formula based values (ie., sort). Your observation of the change in CC’s received dividend becomes a different stocks value is true. I would like to make modifications to get this to work and was reaching out to the community for help on how to do that.

Peter: I am not sure how your suggested formula helps preserve the row data during sort. Also, I can’t tell if the named fields are cells or ranges. If cell only, it seems to produce the value of “1” for all rows having “Div” in column M. The function of the Div Counter was to automatically populate the other two worksheets with only dividend producing assets.
 
fixthis
If You 'sort' only part of columns as You've done - then You have done Yourself something which won't work.
One possible solution would be that those 'Fixed Part' could 'bring' as You've done with Your formulas (from own sheet).
 
I probably used entire columns to generate the ranking as an array but changing criterion values to single cells will give a filled range (which works within a Table). The formula for 'Div Counter' was meant to provide an illustration of how a formula could be constructed to be independent of the sort order. The attached is a Table, I can't remember whether the earlier version was a table or a range.
 

Attachments

Peter,
I see what you did, you made a true array for Div Counter using named ranges. Very good technique.
I can sort by any column on Master and it does not affect the other two Dividend sheets. However, what I need is if I sort Master by Security Type (Z-A) to "group" together let's say all 'Stocks' 'REIT' etc, to have the Dividend Per Share and Dividends Received mirror that order yet retain the corresponding calculated cells.
 
The dividend sheets contain data input in a predetermined sequence which, by definition, will not be mirroring anything. To display the dividends in more general sort order, you will need to form a fresh output table that reads from the Master to determine the display order and then looks up the corresponding monthly data (blue numbers) by index from the static table using 'div counter'.
 
I agree and I think I follow what your saying. Could you please show me with the sample provided so I may get a clearer understanding.
I forgot to mention that I am using Excel 2019.
 
You must log in or register to reply here.
Back
Top