Making a calculator in excel?

**sandbach** · 09-23-2009, 04:21 PM

can anyone help?!!!!!

im trying to make a calculator in excel but not getting anywhere.

basically i want to type a number in a cell and for it to automatically deduct it from a total. i then enter another number in to the same cell and for it to automatically deduct it from the revised total.

is there anyway to do this? and does anyone know how to do it?

sorry if this is posted in the wrong place

kind regards

sandy

**ChemistB** · 09-23-2009, 04:52 PM

You'll need to use VBA.
Right click on the sheet tab and View code and paste this code in

  Option Explicit
  
  Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     Dim TotalVal As Double
     TotalVal = Range("A1").Value
      With Target
         If .Address(False, False) = "A2" And IsNumeric(.Value) Then
               Range("A1").Value = TotalVal - .Value
         End If

      End With
End Sub

This code assumes you are entering a value into cell A2 and the sum of all values is going into A1. Modify as you need. Does this work for you?

**sandbach** · 09-24-2009, 03:44 AM

thanks, that is absolutely brilliant, i really appreciate that!!

**sandbach** · 09-24-2009, 06:30 AM

i have had a fiddle around with it and have got it working to how i want it too with using one calculation, but i have another two independent categories, that i want to do the same too as the first one, i have had a go but cannot do it? can you help with this or can anyone else?

Category 1 Weekly Target 2100
Category 2 Weekly Target 500
Category 3 target 500

Category 1 Completed
Category 2 Completed
Category 3 Completed

Category 1 Revised Target 1178
Category 2 Revised Target 500
Category 3 Revised Target 500

So i substituted A1 for B9 and A2 for B5, so i have category 1 doing what i want it to do, when i enter a value in category 1 completed it takes it off category i revised target.

but what i also want to do is put in a value in category 2 completed (cell B6) and for it to take it off category 2 revised target (cell B10). all completely independent of category 1

and then to do the same for category 3

i hope this makes sense, i do have trouble explaining myself and if anybody can help with a revised code i would be very grateful

kind regards

sandy

**ChemistB** · 09-24-2009, 09:38 AM

Can you post an example workbook (manage attachments) showing what you have and what you want to happen? That will help me. It sounds like you will need to just repeat pertinent lines of code for each of your three categories.

**sandbach** · 09-24-2009, 09:50 AM

Hello, thanks very much for your help and time chemistb i really do appreciate it.

the bit im using is on sheet 1

Basically we have three types of work category 1, category 2, and category 3
I iwant to input the beginning weekly target as in cells B1,2,3.

I then want to input the value of category 1 work we have done from 6 different agents each day, in cell b5, and this too subtract from the beginning total leaving a revised total in cell b9.

so using your coding, i have been able to do this perfectly.

I now want to repeat the process for the other two independant categories 2 and 3. so i want to input the value of category 1 work in cell b6 , and this too subtract and leave a revised total in cell b10. and so on for category 3

the way you have made it so that i overtype the input values is brilliant and just what i wanted it to do, i just need to be able to do it for the other independant categories.

i have had a go myself and looked up on various websites but havent got anywhere, so anymore help really would be appreciated

kind regards

sandy