Solved how do i subtract from a constant cell in excel

May 30, 2016 at 04:34:40
Specs: Windows 7
In Cell A1 I have my daily target say 1000
when i enter number in a2,a3,a4 etc the number should get deducted automatically. for example

I have 1000 in A1 when i enter 50 in A2 it should give me 950 and when i enter 70 in a3 it should give me 880 (1000-50-70) and when i reach 0 it should give me a message target achieved.

See More: how do i subtract from a constant cell in excel

Reply ↓  Report •

May 30, 2016 at 07:04:38
✔ Best Answer
I can think of 2 methods, one using VBA (a macro) and the other using Conditional Formatting.

Macro Method:

Right click the sheet tab for the sheet you want this to happen in. Paste this code into the pane that opens. Whenever you enter a value in A2 or below, the SUM of the values below A1 will be subtracted from 1000 and the value will show in A1.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column A
 If Target.Column = 1 Then
   Application.EnableEvents = False
'Determine how many values are in Column A and Subtract SUM from A1
     lastRw = Range("A" & Rows.Count).End(xlUp).Row
     Range("$A$1") = 1000 - WorksheetFunction.Sum(Range("$A$2:$A" & lastRw))
'Display message if A1 <= 0
       If Range("$A$1") <= 0 Then MsgBox "Goal Reached"
   Application.EnableEvents = True
 End If
End Sub

Conditional Formatting Method

1 - Place 1000 in a cell other than A, e.g. B1. You can hide the cell or change the text to White if you don't want to see the 1000.
2 - Enter this formula in A1 if using B1. Adjust to fit your situation.


3 - Enter your message in a cell that will always be within the viewable area of your screen, e.g. C1.
4 - Format the cell as White text to hide the message.
5 - Conditionally format the cell using the following formula, and use a format to fill the cell with a color of your choice. When the formula is TRUE, the White text will appear within the filled background:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

May 30, 2016 at 08:40:47
Thanks a ton!!! worked wonderful

Reply ↓  Report •

Related Solutions

Ask Question