Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Thanked 0 Times in 0 Posts

    Account Family's: Multiple applications

    Attachment 46503

    All -

    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

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    New York, New York, USA
    Thanked 17 Times in 17 Posts
    Dear Antmart2
    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.


    Tom Duthie
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts