I decided to display the values separate rather than summing them so that problem is resolved.
That said, I now have a much more challenging problem to tackle and any assistance is much, much, appreciated. I am almost positive this will require VBA, but if you can do it with a formula alone, I will be extremely surprised.
Basically, I am trying to get a way to copy all stock names from two sources in alphabetical order, without duplicates, to a final table. The stock names come from two sources: a positions table, and a history table.
Criteria:
1) It should only get stocks for accounts that exist in the Accounts Table (applies to the next two criteria).
2) It should only get stocks from the Positions table from the predefined quarter/year (in the workbook, $P$16 and $P$17)
3) It should only get stocks from the History table if the transaction quarter/year match the predefined quarter/year. If the stock has a settlement quarter/year other than 0, it should only get the stock if those numbers (rather than the transaction quarter/year numbers) match the predefined quarter/year.
4) When it's done, it should remove any rows in the final table in which the formulas for Pre Value and Post Value both return the value 0. For my actual need, those are not the formulas that will be in the Pre/Post Value cells, if you need those I can PM them to you but they are quite lengthy and intricate and there are actually more formula columns than that. In my case, if all the formula columns returned 0 the entire row should be removed.
I'm sure that doesn't make 100% sense so please see the attached workbook. I have an example Position table, History table, Accounts table, Formulas table (though you shouldn't use it, that's just for the formulas in columns of the resulting table), and what the final result table should look like before one of its rows is deleted. After looking at it, everything should be very clear.
SampleWorkbook_ExcelFox_aaronb.xlsx
Reply With Quote
Bookmarks