EXCELL FORMULA NEEDED

Associate
Joined
25 Jun 2006
Posts
1,359
Location
Somewhere East of Eden
Hi, can any one help me?

I need the formula for a simple spreadsheet. I've created a 3 column savings spreadsheet. Column A shows the name of my Banks, Column C shows the current balance of each account. I've managed to enter the formula in the cell at the bottom of Column C to show the total of all bank balances.

I would like to be able to enter a figure (the amount of money I've sent by post, interest received etc) in the cell in Column B so that it automatically adds itself to Column C and then clears Column B for re-use. Is this possible?

Thanx
 
Associate
Joined
30 Dec 2003
Posts
1,368
Location
BC, Canada
Sorry for not being too much help, but I think what you are after is a macro rather than a formula. You could record one to do what you want and then run it when you need to, otherwise you might be able to find something similar on Google
 
Associate
Joined
28 Jun 2005
Posts
895
I'm pretty sure you are wasting your time trying to make a formula to do that. It just can't do that kind of thing.
You will have to make a macro, something to trigger when you leave the cell.

Having a quick look in Excel I suspect you need to make some VBA code to trigger when the spreadsheet is changed. This gets past a parameter which I assume is the cell changed. So you check that to see if it is in column B and then run code to do what you want.
I haven't looked at VBA in a long time so I can't remember the specifics so maybe someone else can help you.

Thats the only way I can see that you can achieve this behaviour. I can't see a small solution (in terms of lines of code) will be particuarly elegant and will only probably work if you do it in an exact way. Personally I would rethink what you are wanting to achieve as this is not how spreadsheets are designed to work.

Ric.
 
Associate
OP
Joined
25 Jun 2006
Posts
1,359
Location
Somewhere East of Eden
|Ric| said:
I'm pretty sure you are wasting your time trying to make a formula to do that. It just can't do that kind of thing.

Personally I would rethink what you are wanting to achieve as this is not how spreadsheets are designed to work.

Ric.

Thanx for your advice. I will rethink and probably just use a calculator and enter the figure in column C as I have been doing.

It was worth a try and there are some Verrrrrrrry clever people on these forums.

Thanx Again
 
Associate
Joined
28 Jun 2005
Posts
895
Well I am feeling in a better (more helpful) mood.

From the toolbar select:
Tools->Macro->Visual Basic Editor

In the new window:
on the left, double click on the sheet that the bank balance is contained in

In the window that has opened on the right:
Select worksheet from the first drop down menu on the left
Now in the drop down menu to the right select "Change"
(it may automatically select SelectionChange or something else and add some code, ignore this and just select "Change")

It should provide you with:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Now add (in the line gap between the first and last gap):
Code:
If Target.Column = 2 Then
  Sheet1.Cells(Target.Row, Target.Column + 1) = Sheet1.Cells(Target.Row, Target.Column + 1) + Target.Cells(1, 1)
  Target.Cells(1, 1) = ""
End If

Close Microsoft Visual Basic (the window that opened when you clicked "Visual Basic Editor"

For this code to work the secruity setting most be no higher than medium. If it is medium every time you open the file you must click "Enable Macros"

From Excel:
Tools->Macro->Security, set it no higher than medium

Close Excel, save changes, re open, enable macros if asked.
Now enter what you want in Column B and it will add it of column C.

Ric.
 
Back
Top Bottom