Results 1 to 2 of 2
2016-02-04, 10:40 #1
- Join Date
- Feb 2016
- Thanked 0 Times in 0 Posts
Account Family's: Multiple applications
First time poster, if what I'm asking is unclear please let me know.
I am trying to create account family's to reduce processing time and make things easier going forward.
I have many instances where I have multiple accounts that all fall under the same restriction umbrella, where the restrictions apply to the total of family funds.
*For example: can invest 5% per a certain identifier per total funds within family.
Ideally, I would be able to create a family and have all of my restriction calculations calculate via family code instead of the individual portfolio codes.
In the attachment, I have made a simplified version of what I am using. The Matrix is populated from accounting software, the software generates excel sheets and the sheets are dropped into the "portfolio data summary" tabs and "security identifier data" tabs
Anyone have an idea what I can do?
February 2016 Matrix Lite.xlsx
2016-02-10, 19:20 #2
- Join Date
- Nov 2002
- New York, New York, USA
- Thanked 19 Times in 19 Posts
Welcome to the Lounge
Attached please find a revised version of you Matrix Lite workbook.
I was not 100% certain what you were looking for concerning your "Ideal" solution so forgive me if this does not completely answer your request.
The attached now uses range names and revised formulas to acheive what I think you are looking for.
Some key items that I think are important.
Using the Excel Function "IFERROR" I have updated all the Vlookup formulas in the Workbook.
Your formula was IF Vlookup ISNA then -0- Else Vlookup
The problem is your makiing Excel do the Lookup function twice. Extensive use of these type of formulas can really slow down Excel. This is the reason Excel now includes the IFERROR function.
On the Holding1 Worksheet the formulas in many cells (see $c$3) are very long and appear overly complex. Again using such formulas in a large number of cells will slow down Excel.
In the workbook I have included new SumIf formulas to replace the original formulas. These are easier to read, understand, and edit. Plus they may speed up computation time.
Lasty, I have made use of Range Names. They make formulas easier to understand and by using the Name Box you can jump to the data rapidly.
If the attached is not what you expected please reply with a better explanation of the current method being used and the "Ideal" method you desire.
I have great confidence that someone in the Lounge can devise fantastic formulas (inlcuding arrays) that may provide a better answer once everyone understands your requirements.
Hope this helps.