How do I show a calculated result in another cell with no formula in the other cell?

[daveygravy]

I am calculating labor hours based on the quantity of a product.
For example, I have 200 tons of material in cell D14. And in cell C27 the labor hours are calculated using =D14*.07 The result will show 14 in C27. The problem I have is, I would like to also show this result, 14, in cell C8, but with no formula in C8. This is to allow a user to change the number in C8 from 14 to another number if they wanted to modify the result. Currently, the formula in C8 is =C27, if the user would change the number in C8, this would delete the formula in C8. C8 is also part of another calculation.
Also, when the original number in D14 is updated, I want to update C8 again with the new result.
Is this possible?

 

[RickOlson]

This should do it. You are watching for changes in D14. When that changes, C27 will change. Then write the value in C27 into C8. You can't watch for changes in C27 because C27 is a formula and changing D14 doesn't change that formula. This should be put in the code for the sheet that you are watching. It will be run every time you make any change to the spreadsheet.

Private Sub Worksheet_Change(ByVal target As Range)
    Dim intersection As Range
    Dim watch As Range
' target is the part of the spreadsheet that was changed
' watch the range of cells that you are looking for changes in
' intersection is the range is cells in watch that were recently changed
    Set watch = Range("C27")
    Set intersection = Intersect(target, watch)

' If there is no intersetion, don't do anything, if the intersection isn't Nothing do something
    If Not intersection Is Nothing Then
' Temporarily stop looking for changes so you don't get in an infinite loop
        Application.EnableEvents = False
' Make the change you want to happen
          Range("C8").Value = Range("C27").Value
' Start looking for changes again
        Application.EnableEvents = True
    End If
End Sub

 

[daveygravy]

This should do it. You are watching for changes in D14. When that changes, C27 will change. Then write the value in C27 into C8. You can't watch for changes in C27 because C27 is a formula and changing D14 doesn't change that formula. This should be put in the code for the sheet that you are watching. It will be run every time you make any change to the spreadsheet.

Private Sub Worksheet_Change(ByVal target As Range)
    Dim intersection As Range
    Dim watch As Range
' target is the part of the spreadsheet that was changed
' watch the range of cells that you are looking for changes in
' intersection is the range is cells in watch that were recently changed
    Set watch = Range("C27")
    Set intersection = Intersect(target, watch)

' If there is no intersetion, don't do anything, if the intersection isn't Nothing do something
    If Not intersection Is Nothing Then
' Temporarily stop looking for changes so you don't get in an infinite loop
        Application.EnableEvents = False
' Make the change you want to happen
          Range("C8").Value = Range("C27").Value
' Start looking for changes again
        Application.EnableEvents = True
    End If
End Sub

Thank you Rick! , I'll try this out.

 

[daveygravy]

Thank you Rick! , I'll try this out.

I made a small change because I enter the number in cell C11 to C18 and C27 calculates it. Thank you for your help. I really appreciate it!!

Private Sub Worksheet_Change(ByVal target As Range)
    Dim intersection As Range
    Dim watch As Range
' target is the part of the spreadsheet that was changed
' watch the range of cells that you are looking for changes in
' intersection is the range is cells in watch that were recently changed
    Set watch = Range("C11:C17")
    Set intersection = Intersect(target, watch)

' If there is no intersetion, don't do anything, if the intersection isn't Nothing do something
    If Not intersection Is Nothing Then
' Temporarily stop looking for changes so you don't get in an infinite loop
        Application.EnableEvents = False
' Make the change you want to happen
          Range("C8").Value = Range("C27").Value
' Start looking for changes again
        Application.EnableEvents = True
    End If
End Sub