1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

EXCELL FORMULA NEEDED

Discussion in 'Windows & Other Software' started by Belly, 6 Jul 2006.

  1. Belly

    Wise Guy

    Joined: 25 Jun 2006

    Posts: 1,311

    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
     
  2. Origin

    Wise Guy

    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
     
  3. LoadsaMoney

    Caporegime

    Joined: 8 Jul 2003

    Posts: 29,924

    Location: In a house

    Ypu might be able to work it out from here :)
     
  4. |Ric|

    Hitman

    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.
     
  5. Belly

    Wise Guy

    Joined: 25 Jun 2006

    Posts: 1,311

    Location: Somewhere East of Eden

    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
     
  6. |Ric|

    Hitman

    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.
     
  7. Belly

    Wise Guy

    Joined: 25 Jun 2006

    Posts: 1,311

    Location: Somewhere East of Eden

    Ric

    It works a treat, thanx very much.

    you're a (gang)STAR !!!

    I didn't know such things existed within Excell.