Connected T Accounts

Hello World!

I'm not sure if this would fall under formulas or VBA, but here's my best attempt at explaining what is going on:

My employer tracks investment cashflows using basic t-accounts. We are still going to input cashflows using independent t-accounts for each investment, but we now want all that data to automatically flow into a "master chart"

If you image, Investment A pays a dividend of $100 on November 1... I'll input that on the T Account (which is a unique worksheet) for Investment A. Investment B pays a $25 dividend on November 4, the same procedure is triggered: I input that activity on a t-account for Investment B. If I print either worksheet (for investments a or b) it will only show data for THAT particular investment. But I also need a "Master Account" which automatically collects new lines from its "child tables" and copies them to the next available line on the Master Chart. The idea is to not be required to enter the same data twice, and thereby avoid human-error in doing so.

Additionally, there will be a constant influx of new investments with their own t-charts so whatever the tool is, be it formula or VBA, needs to be relatively easily "reprogrammed" to look at new worksheets.

Lastly, I *think* I want to accomplish this with a pivot table, which would allow me to slice and dice the data in the future on an ad hoc basis... but I don't know how to get the data from the individual t-accounts to flow into a table.

Attached is a sample mock-up of what I'm trying to get to. Any help is much appreciated!

Hi royUK!

Thank you for the help! I do need to maintain the individual T-Accounts because that's basically what management wants, lol. But in all honesty it does help us more directly track unfunded commitment, paid-in capital, and the call as a % of commitment. This could be accomplished with a slick new pivot table and VBA and kinda have it setup as a form/report but I've tried implementations like that and it honestly just scares off management because they're so unfamiliar with those kinds of tools. So I've resolved to try to keep it "low tech" and more familiar for them, which is an entirely different set of challenges.

Hi Carim!

That indirect formula helps A TON! Thank you!

Hi Carim,

That's a nifty bit of code! In the full version I created a table of contents that hyperlinks to the desired tab (there are 66 existing t-accounts, with more to come), which just made it easier to get to the correct tab to input accounting entries.

But to answer your question, yes, it is simply a recap all the entries from the "child" t-accounts. With the headers filtering turned on, yes I will most likely have them sorted by date, but I might also want to sort by "transaction description" and gather up all the Capital Calls for a given period, for example. On the newly attached file (which includes your VBA), you can see I added an additional attribute column on the Master Table.

Ah yes, you're right! I saw that you did that in the file you sent back, just didn't make that change in the version where I added your VBA. Obvious version control issues on my end, lol, thanks!

[h=1][/h] Thanks Carim!

Two questions:
-I'd like to thank you by sending a CashApp payment so if you want to direct message it to me or share it here, look for a cash thank you from me!
-Would it be smart to put the "Index Funds" button on the Admin tab and let it populate the names directly onto the table? The Indirect formula would then automatically pickup the 'Committed' and 'Callable' amounts?

Thanks Carim!

First, lets not call it 'compensation,' and just a "tip." For real, drop you CashApp handle.

It works amazingly!

Last thought:

There's an "Investments List" on the Master Table worksheet that hyperlinks to each respective fund-specific t-account. But these are the same as the Funds Table on the Admin worksheet. Would it be possible to just have the Funds Table on the Admin worksheet have the Names hyperlinked, cutting down on redundancy?