I write up service invoices and estimates. I want to be about to put a dollar
amount into a specific cell and it automatically adds my 20% to that number -
possible OR must use two cells. I don't want my estimates to show that I have
a 20% mark up on my part cost and I don't want to have to calculate each time
I enter my parts!

PLEASE & Thank You...

Hi,

You can do that with the worksheet change event. Right click your sheet tab,
view code and paste the code below in. Any value entered in Column D will
have 20% added.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = Target.Value * 1.2
Application.EnableEvents = True
End If
End If
End Sub

Mike

"ftcp3" wrote:

I write up service invoices and estimates. I want to be about to put a dollar
amount into a specific cell and it automatically adds my 20% to that number -
possible OR must use two cells. I don't want my estimates to show that I have
a 20% mark up on my part cost and I don't want to have to calculate each time
I enter my parts!

PLEASE & Thank You...

Hi,

If you don't want to use code that you can enter cost in another column
which you hide, for printing purposes. Then in the column you are displaying
you can use the formula
=1.2*C1

where C1 is the cell where you enter the cost.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"ftcp3" wrote:

I write up service invoices and estimates. I want to be about to put a dollar
amount into a specific cell and it automatically adds my 20% to that number -
possible OR must use two cells. I don't want my estimates to show that I have
a 20% mark up on my part cost and I don't want to have to calculate each time
I enter my parts!

PLEASE & Thank You...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = "A1:A10" 'adjust to suit
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Not IsNumeric(Target.Value) Then GoTo ws_exit
With Target
.Value = .Value * 1.2
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click the sheet tab and "View Code"

Copy/paste into that sheet module.

Edit the range to suit then Alt + q to return to the Excel window.

Enter a number in any cell of the range and it will add 20%


Gord Dibben MS Excel MVP

On Thu, 4 Dec 2008 12:39:01 -0800, ftcp3
wrote:

I write up service invoices and estimates. I want to be about to put a dollar
amount into a specific cell and it automatically adds my 20% to that number -
possible OR must use two cells. I don't want my estimates to show that I have
a 20% mark up on my part cost and I don't want to have to calculate each time
I enter my parts!

PLEASE & Thank You...