Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Rows and Maintaining Correct Balances
I'm new to Excel and am still learning. I am working in Excel 2000. What I've
done is make up a spreadsheet for my checking account which, is working quite fine except for a little problem that I have been experiencing. Sometimes I post payments and deposits in advance. For example I may make a posting for a November payment and am still in the month of October. Now that I have entered the Nov payment and, then want to add another row to put in an October payment, I find that the balance is incorrect and, the only way to try and correct it is to copy the previous balance and paste it. However, my continuing balance remains incorrect. In short, what I am trying to do is to insert rows before payments that have already been entered and, at the same time maintain the correct balance. Will appreciate any suggestions. Thanks -- Floridaguy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Rows and Maintaining Correct Balances
Enter this formula in Column A below your payments or a cell like C1
=SUM(A1:INDEX(A:A,ROW()-1)) If you insert a row in column A and add a value, the formula will adjust to include that new value. Gord Dibben MS Excel MVP On Wed, 31 Oct 2007 12:01:18 -0700, Floridaguy wrote: I'm new to Excel and am still learning. I am working in Excel 2000. What I've done is make up a spreadsheet for my checking account which, is working quite fine except for a little problem that I have been experiencing. Sometimes I post payments and deposits in advance. For example I may make a posting for a November payment and am still in the month of October. Now that I have entered the Nov payment and, then want to add another row to put in an October payment, I find that the balance is incorrect and, the only way to try and correct it is to copy the previous balance and paste it. However, my continuing balance remains incorrect. In short, what I am trying to do is to insert rows before payments that have already been entered and, at the same time maintain the correct balance. Will appreciate any suggestions. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Rows and Maintaining Correct Balances
Sent too soon:
Then with the starting balance in E2, enter in E3: =IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2)) and copy down on the fill handle as far as required, (which can be futher than your existing data). -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... It is difficult to give an answer without more detailed info on how your sheet is laid out but assuming that: Column A: Dates Column B: Name/Transaction Column C: Deposits Column D: Payments out Column E: Balance Then with the starting balance in E2, enter in E3: =IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Floridaguy" wrote in message ... I'm new to Excel and am still learning. I am working in Excel 2000. What I've done is make up a spreadsheet for my checking account which, is working quite fine except for a little problem that I have been experiencing. Sometimes I post payments and deposits in advance. For example I may make a posting for a November payment and am still in the month of October. Now that I have entered the Nov payment and, then want to add another row to put in an October payment, I find that the balance is incorrect and, the only way to try and correct it is to copy the previous balance and paste it. However, my continuing balance remains incorrect. In short, what I am trying to do is to insert rows before payments that have already been entered and, at the same time maintain the correct balance. Will appreciate any suggestions. Thanks -- Floridaguy |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Rows and Maintaining Correct Balances
Having seen Gord's post I realise that I was misunderstanding what you were
saying about inserting a row, my formula was designed to accept entries but not add the amount in until the payment date. Better to go with Gord's answer. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Sent too soon: Then with the starting balance in E2, enter in E3: =IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2)) and copy down on the fill handle as far as required, (which can be futher than your existing data). -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... It is difficult to give an answer without more detailed info on how your sheet is laid out but assuming that: Column A: Dates Column B: Name/Transaction Column C: Deposits Column D: Payments out Column E: Balance Then with the starting balance in E2, enter in E3: =IF(COUNT(C3:D3)=0,"",IF(A3<=TODAY(),E2+C3-D3,E2)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Floridaguy" wrote in message ... I'm new to Excel and am still learning. I am working in Excel 2000. What I've done is make up a spreadsheet for my checking account which, is working quite fine except for a little problem that I have been experiencing. Sometimes I post payments and deposits in advance. For example I may make a posting for a November payment and am still in the month of October. Now that I have entered the Nov payment and, then want to add another row to put in an October payment, I find that the balance is incorrect and, the only way to try and correct it is to copy the previous balance and paste it. However, my continuing balance remains incorrect. In short, what I am trying to do is to insert rows before payments that have already been entered and, at the same time maintain the correct balance. Will appreciate any suggestions. Thanks -- Floridaguy |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting Rows and Maintaining Correct Balances
Thanks for your help.
It seems to be working. -- Floridaguy "Gord Dibben" wrote: Enter this formula in Column A below your payments or a cell like C1 =SUM(A1:INDEX(A:A,ROW()-1)) If you insert a row in column A and add a value, the formula will adjust to include that new value. Gord Dibben MS Excel MVP On Wed, 31 Oct 2007 12:01:18 -0700, Floridaguy wrote: I'm new to Excel and am still learning. I am working in Excel 2000. What I've done is make up a spreadsheet for my checking account which, is working quite fine except for a little problem that I have been experiencing. Sometimes I post payments and deposits in advance. For example I may make a posting for a November payment and am still in the month of October. Now that I have entered the Nov payment and, then want to add another row to put in an October payment, I find that the balance is incorrect and, the only way to try and correct it is to copy the previous balance and paste it. However, my continuing balance remains incorrect. In short, what I am trying to do is to insert rows before payments that have already been entered and, at the same time maintain the correct balance. Will appreciate any suggestions. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide rows with zero balances | Excel Discussion (Misc queries) | |||
Pivot Tables - Exclude Rows With Zero Balances | Excel Discussion (Misc queries) | |||
HOW DO I AUTOMATICALLY DELETE ROWS WITH ZERO BALANCES? | Excel Worksheet Functions | |||
Maintaining cell references when inserting cells elsewhere | Excel Discussion (Misc queries) | |||
How to link endings balances to beginning balances on different sh | Excel Worksheet Functions |