Could someone help me with this idea i was hoping excel could manage this.
For my company i can export from my database into excel a list of all cheques printed in a date range. i would then like to use this list to "cross off" the cheques on a daily basis when i am doing the bank reconciliation so that on a daily basis i could see the total outstanding cheques.
i have created an excel sheet like this:
Column A lists the Check numbers
Column B lists the Date of the check
Column C lists the Check amount
Bottom of Column C is a grand total amount (i.e checks outstanding)
once i have this long list, could be anywhere from 10 to 500 cheques(rows), on a daily basis i would like to type in, lets say column d row 1, the first check number (which has cleared the bank) and then excel would locate that check from column A and "delete" it so that the amount in column c would not show in the grand total at the end of column C. then i would continue overwritting in column d row 1 typing in the second check number to clear the bank. therefore once i have done all the checks for the day, the grand total at the bottom of column C would show the remaining outstanding balance of all the checks.
This sheet would also have to be able to expand on a weekly basis, as we print more checks i would add them to the bottom of rows form the previouse checks and continue on.
I concider myself a medium user of excel and can understand complex uses of excel if someone told me a "pro" formula, even if i cant im sure my IT guys at work could.
I figure to accomplish this task id probbaly need a macro? but not sure if i could even use that in this case. I thought of using Vlookup but i would have to almost have a conditional formating on the vlookup result which would remain even after the vlookup value is changed... which is where i got lost if this is possible. i could also use access if you think that it is only possible in access?
If i lost you guys somewhere, i would be happy to try and explain with illustrations.... but hopping i was clear enough?
please help
thanks!
For my company i can export from my database into excel a list of all cheques printed in a date range. i would then like to use this list to "cross off" the cheques on a daily basis when i am doing the bank reconciliation so that on a daily basis i could see the total outstanding cheques.
i have created an excel sheet like this:
Column A lists the Check numbers
Column B lists the Date of the check
Column C lists the Check amount
Bottom of Column C is a grand total amount (i.e checks outstanding)
once i have this long list, could be anywhere from 10 to 500 cheques(rows), on a daily basis i would like to type in, lets say column d row 1, the first check number (which has cleared the bank) and then excel would locate that check from column A and "delete" it so that the amount in column c would not show in the grand total at the end of column C. then i would continue overwritting in column d row 1 typing in the second check number to clear the bank. therefore once i have done all the checks for the day, the grand total at the bottom of column C would show the remaining outstanding balance of all the checks.
This sheet would also have to be able to expand on a weekly basis, as we print more checks i would add them to the bottom of rows form the previouse checks and continue on.
I concider myself a medium user of excel and can understand complex uses of excel if someone told me a "pro" formula, even if i cant im sure my IT guys at work could.
I figure to accomplish this task id probbaly need a macro? but not sure if i could even use that in this case. I thought of using Vlookup but i would have to almost have a conditional formating on the vlookup result which would remain even after the vlookup value is changed... which is where i got lost if this is possible. i could also use access if you think that it is only possible in access?
If i lost you guys somewhere, i would be happy to try and explain with illustrations.... but hopping i was clear enough?
please help
