Gfrktyl

Basically I want to be able to input the pay rate (ex: enter "14" to symbolize 14 dollars per hour) into the cell that already has a formula to get a result (In the same cell.).

If I input 14 in D20 I'll get 2426.67 as the result.

The formula is (((inputed value) * (40) * (52)) / (12))

• 40 weekly hours


• 52 is weeks in a year


• 12 is monthly

Is it possible?

MS Excel doesn't work that way. Once you input something in to the cell, it overwrites it's contents.

You may be able to write a macro to do this, as suggested by @datatoo, however you still have the issue of the cell changing values when the answer is determined, which may trigger another calculation.

Why do you want to do this? Maybe we can suggest an alternate method for you.

Any cell can have either a Formula or a Value, not both, This is how basically the Excel works for.

Now to attain what you describe, you would need VBA (Macro) to do the calculation when the cell value is changed.

The VBA code I'm suggesting in bit improvised and it works on entire Column or on any particular Data range, rather than only on a Cell, also prevents from Non numeric data.

Private Sub Worksheet_Change(ByVal Target As Range)



 If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

 If IsNumeric(Target.Value) Then



   Application.EnableEvents = False

     Target = (Target * 40 * 52) / 12

      Application.EnableEvents = True

 Else

         MsgBox ("Only calculate numeric values")

 End If



End Sub

N.B.

• Copy & Paste this Code as Standard module.


• 
  Range("A:A") is editable and should Rage("A:C") or even Range("A1:C10") also.

It is possible only with using VBA and listening out for the the Worksheet_Change Event.

Give the below code a try, I have added informative comments in hopes you will learn how it works.

Private Sub Worksheet_Change(ByVal Target As Range)

    ' The below If statement uses Intersect to check, 

    ' if the cell being changed is NOT cell D20 it gets ignored.

    If Not Intersect(Target, Target.Worksheet.Range("D20")) Is Nothing Then

        Application.EnableEvents = False 'Disables events to prevent endless loop



        On Error GoTo Finalise 'Re-enable events



        ' The below code gets the value from cell D20,

        ' and stores the value to the inputVal variable.

        inputVal = Range("D20").Value 



        ' The below code does your calculation, 

        ' and stores the value to the newValue variable.

        newValue = (inputVal * 40 * 52) / 12 



        'Changes the value in cell D20 to the value of the newValue variable. 

        Range("D20").Value = newValue 

    End If



Finalise:

    Application.EnableEvents = True

End Sub

Note: This code has to go into the sheet itself and not in a module.

Edit: Steps from comment.

1. Go into the Developer Tab.


2. Click on Visual Basic button to open,


3. In the Visual Basic window that pops up look at the Project - VBAProject pane (on the left side of the window),


4. Find your Worksheet, double click on it,


5. Paste the above code in, save your workbook.