I am trying to create a pivot table from a daily downloaded Excel sheet that is in reality a simple table containing rows of stock positions sorted by account. The number of rows for each account will change according to added or deleted positions. I have a pivot table that organizes the data by a "short name" for each account and subtotals the value of the account. I need to add columns that determine the percent of the subtotal in each position, calculate a threshold percent position limit, calculate over allocations above that, and specify position reduction amount of the over-allocation. These depend on the short name field subtotals. The problem is that as the PT is refreshed with a new download that has more or fewer positions (rows) in an account, it changes the cell reference for the subtotal, while the added column calculations refer to the old cell reference. And the calculation cells may shift to rows related to another account, so the values on that row may now refer to a different short name grouping, or a non-subtotal row.
Is there a way to provide a dynamic cell reference to the percentage calculations which keeps up with the new subtotal location and checks that it is in the same short name grouping. A small section of the spreadsheet is attached. The $F$10 reference in column P is to the field subtotal; the second $A$3 in Q is to the Short Name cell which occurs only on the first line of the field grouping. Each of these references will change if rows are added or subtracted from the source table, and the new reference need to be used in the subtotal percentage calculations.
Is there a way to do this? Thanks!
