Hi
Yesterday I learnt how to copy data from one cell to another based on what was currently selected in a data validation cell. This has turned out to be very useful.
I've got a simple accounting sheet that I call the General Ledger. The column headings are Date, Invoice Number, Name, Address, Phone Number, Amount, Payment Type and a Payment Received data validation cell that says either Yes or No.
I then have another sheet called "Money Outstanding"
I want to copy the "General Ledger" data from the Date, Invoice Number, Name, Address, Phone Number and the Amount columns and paste that onto the "Money Outstanding" sheet. I can do this by using this formula. (This copies the date and I would use a similar one for the other columns)
=SUMIF('General Ledger'!$I9:$I304,"No",'General Ledger'!$B9:$B304)
The data validation column on the general ledger is column "I". The date column on the General Ledger sheet is column "B". I'm adding this formula into the first cell available on the Date Column in the Money Outstanding sheet which is cell B6.
I have encountered a problem.
As the data is copied from the General Ledger to Money Outstanding I want it to populate the next available line on the Money Outstanding sheet. As someone pays me and I change the data validation on the General Ledger to YES I want the corresponding line on the Money Outstanding sheet to disappear.
I have no idea if this can be done fairly easily or if its a big job. Any help appreciated. Thanks
Yesterday I learnt how to copy data from one cell to another based on what was currently selected in a data validation cell. This has turned out to be very useful.
I've got a simple accounting sheet that I call the General Ledger. The column headings are Date, Invoice Number, Name, Address, Phone Number, Amount, Payment Type and a Payment Received data validation cell that says either Yes or No.
I then have another sheet called "Money Outstanding"
I want to copy the "General Ledger" data from the Date, Invoice Number, Name, Address, Phone Number and the Amount columns and paste that onto the "Money Outstanding" sheet. I can do this by using this formula. (This copies the date and I would use a similar one for the other columns)
=SUMIF('General Ledger'!$I9:$I304,"No",'General Ledger'!$B9:$B304)
The data validation column on the general ledger is column "I". The date column on the General Ledger sheet is column "B". I'm adding this formula into the first cell available on the Date Column in the Money Outstanding sheet which is cell B6.
I have encountered a problem.
As the data is copied from the General Ledger to Money Outstanding I want it to populate the next available line on the Money Outstanding sheet. As someone pays me and I change the data validation on the General Ledger to YES I want the corresponding line on the Money Outstanding sheet to disappear.
I have no idea if this can be done fairly easily or if its a big job. Any help appreciated. Thanks
