Dear Employer,
I am an excel developer with the Retail business / Supply Chain / Inventory control / Replenishment Planning and Sales Data analysis back ground. I always use Excel both excel standard and VBA coding to design the simulation model for the business and also create the dynamic Spreadsheet for users to use.
You can see more of my profile and expertise here: my project feedback in http://www.freelancer.com/users/feedback_2698784.html
I have read your requirement and your attachment...it is clear that the way you use the "Sheet1!" formula is not suitable for your work...let me design this for you...
I will write the VBA code this way...
1) you can daily input in the Cash Book or Weekly depends on your work process.
2) on the Cash book, you will have 1 button, of course assign VBA code, so that after finish inputting the data to the Cash Book, just push the button.
3) Then the code will check all transaction on cash book, then correctly book them to relevant sheets.
4) remove the already booked, from the Cash book...
5) Then next time you push the button, the new data goes to the last line of each ledger automatically.
Of course, for this time, you also just push one click then all will be book to each ledger correctly.
Hope to have chance working for you..
Kind regards,
Pichart Y.