Home |
Search |
Today's Posts |
#1
|
|||
|
|||
nOOb formulas question
Ok can you walk me through this I am very new to this.
I have sheet 1. I have a starting balance on cell H1. Also on that sheet I have a Total on cell D3. Sheet 2 Column E will be my Recieved and Column G will be my paid. I dont know what the sum column is for. I need to have any number entries on Column E sheet 2 added to D3 sheet 1, and subtract from H1 sheet 1. Example: I have starting balance of $1000 in H1. And a Total on D3 of $500. I add $20 to any cell in Column E. We will say E2. Now H1 equals $980, E2 equals $20, and D3 equals $520. I barely know where to start so please be patient and make your explanations detailed Thank you so much. "Arvi Laanemets" wrote: Hi It is possible with worksheets change event. But I myself consider this as very bad idea - when you enter some wrong number, it will be practically impossible to track down the error later. Instead I advice to use the design, where all transactions are gathered into single table, and running total is calculated from this table. As bonus, you can design reort sheets, where p.e. all transactions for selected period, along with totals for period start and end dates, are displayed. An example of transaction table: Sheet: Transactions Date, Operation, Sum, Comment The column Operation contains validation list with 2 selections: "payd" and "recieved"; Sum is always positive. At start you enter the start value for your account (as "recieved"). In Comment field you can enter some explanation, so you don't get confused later. The table will be simplest to calculate, when you never don't have any transactions earlier than start transaction, and you have only one start transaction at any time. You define dynamic named ranges (p.e. Date, Operation and Sum) for tables datarange, using Date column as check range (so that all 3 named ranges are always of same dimension) The total will be: =SUMIF(Operation,"recieved",Sum)-SUMIF(Operation,"payd",Sum) When the number of rows in table grows, and you want to remove some elder transactions, then you have to calculate the total for date, which will be new start date (date0), enter the new start value, and delete all rows with dates earlier as new start date (you can write a procedure for this). The worksheet formula to calculate total for some fixed date (FixedDate) is: =SUMPRODUCT(--(Date<FixedDate),--(Operation="recieved"),Sum)-SUMPRODUCT(--(D ate<FixedDate),--(Operation="payd"),Sum) Arvi Laanemets "Patrick" wrote in message ... I am creating a spreadsheet for my acount payables and recievables. I want to designate a cell to keep a total. I need one cell whenever I add a figure it add to the total and another cell whenever I subtract a figure it will subtract from that same cell. |
#2
|
|||
|
|||
Hi Patrick
what concerns me about your question is where the 1000 is coming from in H1 and the 500 in D3 ... however, assuming that you want to hard-code these into the formula in the cell then the formulas to use in Sheet1!H1 is =1000-SUM(Sheet2!E:E) and the formula to use in Sheet1!D3 is =500+SUM(Sheet2!E:E) Cheers JulieD "patrick" wrote in message ... Ok can you walk me through this I am very new to this. I have sheet 1. I have a starting balance on cell H1. Also on that sheet I have a Total on cell D3. Sheet 2 Column E will be my Recieved and Column G will be my paid. I dont know what the sum column is for. I need to have any number entries on Column E sheet 2 added to D3 sheet 1, and subtract from H1 sheet 1. Example: I have starting balance of $1000 in H1. And a Total on D3 of $500. I add $20 to any cell in Column E. We will say E2. Now H1 equals $980, E2 equals $20, and D3 equals $520. I barely know where to start so please be patient and make your explanations detailed Thank you so much. "Arvi Laanemets" wrote: Hi It is possible with worksheets change event. But I myself consider this as very bad idea - when you enter some wrong number, it will be practically impossible to track down the error later. Instead I advice to use the design, where all transactions are gathered into single table, and running total is calculated from this table. As bonus, you can design reort sheets, where p.e. all transactions for selected period, along with totals for period start and end dates, are displayed. An example of transaction table: Sheet: Transactions Date, Operation, Sum, Comment The column Operation contains validation list with 2 selections: "payd" and "recieved"; Sum is always positive. At start you enter the start value for your account (as "recieved"). In Comment field you can enter some explanation, so you don't get confused later. The table will be simplest to calculate, when you never don't have any transactions earlier than start transaction, and you have only one start transaction at any time. You define dynamic named ranges (p.e. Date, Operation and Sum) for tables datarange, using Date column as check range (so that all 3 named ranges are always of same dimension) The total will be: =SUMIF(Operation,"recieved",Sum)-SUMIF(Operation,"payd",Sum) When the number of rows in table grows, and you want to remove some elder transactions, then you have to calculate the total for date, which will be new start date (date0), enter the new start value, and delete all rows with dates earlier as new start date (you can write a procedure for this). The worksheet formula to calculate total for some fixed date (FixedDate) is: =SUMPRODUCT(--(Date<FixedDate),--(Operation="recieved"),Sum)-SUMPRODUCT(--(D ate<FixedDate),--(Operation="payd"),Sum) Arvi Laanemets "Patrick" wrote in message ... I am creating a spreadsheet for my acount payables and recievables. I want to designate a cell to keep a total. I need one cell whenever I add a figure it add to the total and another cell whenever I subtract a figure it will subtract from that same cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically 'incrementing' formulas for new rows. | New Users to Excel | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |