Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Best Way For Excel To Compute Ownersips (2003)

    Can anyone assist with the best approach using Excel to determine amounts a company owns through a chain of related companies.

    A small example is attached.

    Any solution would need to be dynamic so any changes would result in updated results. The actual project I am working on will be for well over 100 companies and 8 to 9 levels.
    Is formulas, pivot tables, database,auto filters, advances filters, array formulas, or VBA the best approach, or perhaps this should be done in Access

    Regards,

    Tom Duthie
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Best Way For Excel To Compute Ownersips (2003)

    No doubt it's possible in Excel, using VBA or perhaps even making clever use of formulas, but I would do this in Access...
    The attached sample database demonstrates an approach using a recursive VBA function (i.e. a function that calls itself).
    The form frmOwnership lets you choose a parent company and child company from combo boxes. The text box below the combo boxes will show the percentage of ownership.

    Notes:
    - There is no check that percentages add to 100%.
    - With many levels, it may take a while to calculate the percentage.
    - The function cannot handle companies owning themselves/circular relationships (A owns part of B directly or indirectly, and B owns part of A directly or indirectly).
    - No safeguards against such situations have been built in - you'll get an error message.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Best Way For Excel To Compute Ownersips (2003)

    Hans:

    Thanks for your assistance and warnings.

    The actual data is a download from other software and has been checked to ensure all ownership is 100%. In addition, by query the other software already confirmed there are no circular ownerships . Too bad the blasted other software won't do the tiered ownership computatons.

    Based on your observations, I fully agree that Access is the best approach for this project.

    If I run into further problems on this one I will post them to the Access Forum

    Regards,

    Tom Duthie

Posting Permissions

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