WeThotUWasAToad
asked on
Merge multiple financial ledgers to a single summary ledger in Excel
Hello,
Is there a way, in Excel, to merge multiple financial ledgers into a single summary ledger?
For example, suppose you have three checking accounts, each identified by the last four digits in its account number, and each with identical standard bank-account column headings as shown here:
Furthermore, suppose that the ongoing transactions for each account are entered — either automatically or manually — into a worksheet according to account number. In other words, if the three account numbers are 5678, 4321, & 9753 and each has its own sheet tab (as shown above) then transactions, as they occur, are entered into their respective worksheet creating a separate and current register for each account.
With that information, how could you create a summary or "combined" worksheet which automatically displays transactions from the three individual accounts as they are entered?
In other words, all entries and edits would be made only in the single accounts but automatically appear in correct chronological order in the summary sheet.
By the way, my preference is to find a solution using only Excel formulas if that's possible, with VBA as the 2nd option.
Thanks
Is there a way, in Excel, to merge multiple financial ledgers into a single summary ledger?
For example, suppose you have three checking accounts, each identified by the last four digits in its account number, and each with identical standard bank-account column headings as shown here:
Furthermore, suppose that the ongoing transactions for each account are entered — either automatically or manually — into a worksheet according to account number. In other words, if the three account numbers are 5678, 4321, & 9753 and each has its own sheet tab (as shown above) then transactions, as they occur, are entered into their respective worksheet creating a separate and current register for each account.
With that information, how could you create a summary or "combined" worksheet which automatically displays transactions from the three individual accounts as they are entered?
In other words, all entries and edits would be made only in the single accounts but automatically appear in correct chronological order in the summary sheet.
By the way, my preference is to find a solution using only Excel formulas if that's possible, with VBA as the 2nd option.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Take a look at the code and example that I have just posted here
ASKER
The follow-up thread is located here:
https://www.experts-exchange.com/questions/29048395/Combine-multiple-sorted-Excel-worksheets-to-a-single-summary-worksheet-which-is-also-sorted.html
https://www.experts-exchange.com/questions/29048395/Combine-multiple-sorted-Excel-worksheets-to-a-single-summary-worksheet-which-is-also-sorted.html
I provided code there as well. Just waiting to see if you have tested it then I can add the sort if required although I would recommend a PivotTable based on the data as more efficient
ASKER
You are right Roy, this question really needs an attached workbook.
Thanks for the link Shaun but I would really like to find a solution within Excel. The reason is that I generally cannot tweak add-ons the way I want.
I think the best route is to close this thread and open a new one with screenshots and an attached file.
Thanks