excel 2010 transfer money atround

  Terry Brown 20:43 12 Aug 2014
Locked
Answered

I have a simple spread sheet when I list all my income and all my outgoings and balance at the bottom.

What I would like to do is add a macro so I could right click on one cell enter the amount I wish to take out, transfer it to another cell and have the balances updated automatically. E.G. In cell (A1) I have bank credit of £200 I want to pay a gas bill(A2) of £36 Click on the bank account enter the £36 then click on the Gas Bill click again and the money is transferred leaving totals of £164 in the bank account and 36 in the gas bill account.

How would I write one please.

Thanks for looking.

  lotvic 21:24 12 Aug 2014

Why a macro?

I have a simple 5 column method. A B C D E

A is for the date

B is for the description

C is for Debits

D is for Credits

E is for the Running Balance

so using your figures of a credit balance of £200 and a gas bill of £36 mine would be:

Row 1, A to E has the column headings Date, Description, Debits Credits, Running Balance.

B2 Credit Balance

E2 £200

B3 Gas Bill

C3 -£36

D3 £0

E3 =SUM(E2+C3+D3)

  Woolwell 12:35 13 Aug 2014
Answer

I'm not entirely sure that is what Terry Brown wants. I think that he wants a separate column headed Gas Bill, Electricity etc.

If you modified lotvic's then you add columns F Gas Bill, G Electricity In F3 the formula =ifB3="Gas Bill",C3,0" and in G3 =ifB3="Electricity",C3,0" should work. However C3 needs to be changed from a minus which saves remembering to enter a minus figure and the E3 sum should be E3-C3+D3. Dragging the formula down updates them for each row.

In F2 =sum(F3:F1000) adds the total of Gas and in G a similar formula does the same for electricity.

  Terry Brown 13:09 13 Aug 2014

Thanks for your help, I had a macro (from this site) some time ago, but have lost it, due to (1) Harddrive crash (2) Change from Office 2003 to Office 2010 and for some reason the macro did not copy across.

Terry

  wee eddie 14:35 13 Aug 2014

We all miss Vog.

  Terry Brown 10:33 17 Aug 2014

I have just found the Macro, I used- I seem to remember it come from Lotvic (I may be mistaken) originally.

I re-print it for any one who wishes to use it.

Written in Visual Basic, operated by double left click

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim MoveAmount As Variant, MoveTo As Range

Cancel = True

MoveAmount = Application.InputBox("Enter amount to transfer", Type:=1)

If TypeName(MoveAmount) = "Boolean" Then Exit Sub

Set MoveTo = Application.InputBox("Select the destination Cell", Type:=8)

MoveTo.Value = MoveTo.Value + MoveAmount

Target.Value = Target.Value - MoveAmount

End Sub

Terry

  lotvic 16:00 17 Aug 2014

Not me, wish it had been :)

pleased you got it sorted anyways.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?