Connected T Accounts

  • In order to be coherent ... the Admin tab has been moved to the first position in workbook...

    Indeed, since your Master Table sheet is entirely dependent on the complete list of Funds,

    the ' Index & Admin ' macro needs to be launched BEFORE the ' Master ' macro ...

    Attached is your Version 4

    Hope this will help

  • Just been having a play with your file.

    If you want something that is straight forward and simple for everybody to use then I think using user forms and multiple buttons is the way to go.

    In the attached file there is a hidden sheet that is a template for creating new T-Account sheets, there is also a hidden sheet named "Report" which holds the Funds Table and is where you should add your pivot table and slicers. The hidden "Admin" sheet contains 3 tables that are used for the user forms comboboxes lists.

    The first button, when clicked, will open a user form where there are options to either update an existing T-Account or create a new one. The other buttons are self-explanatory.

    Note all the T-Account sheets can remain hidden so the workbook is not cluttered.

    If you ever need to print the Master sheet then the buttons will not get printed.

  • Again, thank you so much Carim! Everything works, its very clean, and exactly what I was hoping to get to!!

    I forgot, what did you say your CashApp handle was?

    Last thing: I added an Investment C using the procedure I outlined earlier, after running the Index & Admin button, Investment C did populate on the Admin tab but it appeared "below" the table instead of "on the table" idk if that makes sense, but file is attached with those changes.

  • KjBox, wow!!!

    If I totally had my say, this would be the implementation. But my managers are .... change averse. They've been using this old t-account setup for more than a decade, they've all been here more than a decade, and they kinda go crazy when you try to change something. I really appreciate your efforts and I'd use it if I thought it had a chance of winning support from my bosses but from my 2 years of being here, I can say that is beyond unlikely.

    Some background for anyone who might even care:

    We are a large healthcare nonprofit that is more than 100 years old, as is common, we invest excess cash because keeping it in "cash" is a losing position when you consider inflation. Our old method was to open an individual account at our custodian bank for each investment. So think of it this way: Your 401(k) might have five mutual funds (for example). Thats 1 account with 5 investments. We used to open a separate account for each investment, so using our method, you would have 5 separate 401(k) accounts, each holding just a one investment! This got to be burdensome and expensive.

    So we're transitioning to a Master Trust structure wherein all the investments are held in a single account, this would move us back towards the 1 account with 5 funds in the above example. It used to be very easy to reconcile investment activity because each account only showed activity for that specific investment. But now we're going to have 66 investments in one account. For audit reasons, we need a "master table" that reasonably matches our custodian bank. But for us to track the individual actions of each investment, we still need to see activity from that perspective.

    I'm going to be creating "closing entries" on all 66 of the old t-accounts and kinda starting fresh with this new structure, but management is extremely touchy about being able to do things the way they've always done them ::insert eye roll::

    Again KjBox, thank you!!!

  • Hello Robert ...
    For sure, you have already noticed ...I am ' deaf of the left ear ' :lol::lol::lol: :smash:

    Consider it as an extremely modest tribute to Henry ... :wink:

    Meanwhile the resize modification you needed is now added ...

    Attached is your Version 5 ... :smile:

  • Thanks for your Thanks ... AND for the Like :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hello Robert ... Happy New Year to you ...!!! :)

    Seems to me you have modified the workbook's structure ...

    Attached is the latest Version 6 which you can hopefully use as your starting point ...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!