Auto-merge multiple transaction ledgers in Excel

WeThotUWasAToad
WeThotUWasAToad used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
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.
Finance Analyst
Commented:
How about a Pivot Table with multiple source tables??
WeThotUWasAToad

Author

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.
WeThotUWasAToad

Author

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.
WeThotUWasAToad

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial