Place a furmula in the neext cell automatically Without Macros

[Vikalp]

I am calculating the total account balance if enter the amount in credit column it get added and vice-versa for the debit amount. The formula to add or subtract is in the cell adjacent to Credit and debit cells. I want to copy this formula in the cell one row below automatically and specially without Macros.


For example B2 is for credit C2 is for debit and D2 is for total account balance and if I input in B2 the amount gets added to D1 (existing account balance) and the result is displayed in D2. I want to get this formula copied over to D3 (total account balance) automatically..


Please advice on how can I do this without Macros…..

 

[villy]

Please show some sample...

 

[Vikalp]

B C D
1 Credit Debit Account Balance
2 5000
3 2500 7500
4 5000 2500

Only B and C are the input cells and D should automatically calculate the total balance

 

[villy]

Try..

[D2]=D1+B2-C2
drag down to copoy formula...

Result:
If [D1]=0
[D2]=5000
[D3]=0
[D4]=2500

 

[Vikalp]

In line 3, 2500 is under credit and 7500 is the account balance
In line 4, 4000 is under debit and 3500 is the account balance

 

[villy]

What do you mean..Where is your data starting? In line 3 is it Row3?

 

[Vikalp]

Thanks villy.... but I want this to happen automatically.. I used the offset to calculate the moving average (average for pre-defined days in past) in that it is working fine but not in this case.. as I am also using nested if to check a) that both credit and debit should not be blank and b) they both should not have values in them only one can have the value at a time. I'll am planning to lock all the cells except Credit and Debit for which I have created dynamic named ranges. This I'll further use these ranges to analyze the data monthly...

 

[Vikalp]

yes it means row 3

 

[pedro-egoli]

Is this along the lines of what you are after

Excel Workbook
	A	B	C	D
1	*	credit	debit	account balance
2	*	*	*	0
3	01-07-11	5000	*	5000
4	02-07-11	2500	*	7500
5	03-07-11	*	5000	2500
Sheet1

I also added Data Validation to columns B and C with custom formula
Starting in B3 =(C3>1) and then in
C3 =(B3>1)

Once these have been entered highlight the range and go to Data/Validation and when given the option to place validation in the range click OK

Pedro

 

[Vikalp]

Hi Pedro, I tried it but it is not working but this is exactly the problem... I tried it all but is there a way to work with dynamic named ranges?? I tried it but but no luck...