Auto-merge multiple transaction ledgers in Excel

Hello,

Is it possible for Excel to auto-combine multiple ledgers into a single summary ledger?

For example, suppose you've got some number of transaction ledgers with the following column headings:

Fig. 1
And suppose each account resides in its own worksheet and is identified by a 4-digit number (eg #1111, #2222, #3333, etc).

Is there a way to have an additional summary worksheet which displays transactions from all ledgers and auto-updates whenever additional or changed transactions are entered?

Note that the following summary screenshot includes an additional column to display 4-digit account numbers:

Fig. 2
Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Not easily. You can do something with VBA which watches the Worksheet_Change event and copies data over to the summary sheet, and visa versa, but I'd be inclined to just have a single sheet with data from all accounts. To display data from a single account you could then apply a filter on the account number column.
Rob HensonFinance AnalystCommented:
How about a Pivot Table with multiple source tables??

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeThotUWasAToadAuthor Commented:
Thanks for the responses.

Expert Comment by: Wayne Taylor (webtubbs)
…I'd be inclined to just have a single sheet with data from all accounts. To display data from a single account you could then apply a filter on the account number column.
Wayne, I considered that option but it would involve frequent sorting and filtering and also multiple Balance columns — all doable but cumbersome and time-consuming.
WeThotUWasAToadAuthor Commented:
Expert Comment by: Rob Henson How about a Pivot Table with multiple source tables??
Time for confessions Rob.  Despite hearing of Pivot Tables on occasion in the past, I've never really understood what they do nor how to create/use them.  However, if this is a good scenario for creating a Pivot Table and if it will do what I'm after, then this is a great time to learn about it.  Therefore, I'm going to close this thread and open a new one shortly with that question.
WeThotUWasAToadAuthor Commented:
Expert Comment by: Wayne Taylor (webtubbs)
You can do something with VBA which watches the Worksheet_Change event and copies data over to the summary sheet, and visa versa,
I suspected that some VBA code may be required and that's OK if it provides the best solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.