Avatar of WeThotUWasAToad
WeThotUWasAToad
 asked on

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
Microsoft OfficeMicrosoft ExcelVB ScriptVBA

Avatar of undefined
Last Comment
WeThotUWasAToad

8/22/2022 - Mon
SOLUTION
Wayne Taylor (webtubbs)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
WeThotUWasAToad

ASKER
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

ASKER
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

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes