Connected T Accounts

Quote from Carim

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

Display More

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.

Quote from KjBox

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.

Display More

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!!!

Carim

HAPPY NEW YEAR!

Now that I'm ready to add the actual funds and data the information isn't flowing up to the Master Table. Attached is the file with my first edit. When I run the macro on the Admin worksheet it will update Table1 just fine. But this new activity is not flowing over to Table2 the Master Table worksheet.