Is it possible to embed a formula?

[LWinston]

I have an excel document that I plan to save as a template to distribute to multiple team members. I'm having a problem protecting a formula inside of a cell. Here's the story:

The cell has a formula in it, but if you type a number in the cell and it's incorrect, when you delete the incorrect entry, it also deletes the formula. How can I embed a formula into a cell so that it stays with a cell no matter what as long is the sheet is protected? Please help. This is driving me nuts. Thank you.

 

[shg]

If you overwrite a formula with a manual entry, it's gone. If you lock the cell and protect the sheet, you can't make a manual entry.

I'm missing what you're trying to do.

 

[LWinston]

so...the cell has a formula in it and how it calculates is based on a drop down selection in a different cell. This is how how the formula looks now: =IF(ISBLANK(A17), "", IF(ISERROR(VLOOKUP(A17,'Corbis Entertainment Billing Worksheet.xlsx'!lookups,2,FALSE)), "ENTER COGS", VLOOKUP(A17,'Corbis Entertainment Billing Worksheet.xlsx'!lookups,2,FALSE)*D17))

Based on what the user selects, the cell will auto-populate or if the user selects a different option, they will have to enter an amount in the cell. Now, if the user is enters an amount that's incorrect and then deletes and corrects, it also deletes the formula and that's what I want to prevent. I want to tie the formula to the cell no matter what. I can't lock the cell because then the user will not have the ability to enter data there at all.

 

[shg]

A cell can have a manual entry or a formula, not both. There is no way to make a formula reappear after it's overwritten.

You could put the optional manual entry in a different cell, and if that cell is populated, its value is used in preference to the cell that contains the formula.

 

[AkaTrouble]

you could have a sheet change event that checked if cell (A1 in this example) = nothing if so put your formula in the cell

this would result in being able to overwrite formula by user but if they delete cell it will replace formula

this means workbook would have to be saved as macro enabled

this code using your formula i can not test fully as you refer to named ranges i don't have

this code goes in sheet module right click tab view code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    
        If Target = "" Then
        
        Range("A1").Formula = _
        "=IF(ISBLANK(A17), """", IF(ISERROR(VLOOKUP(A17,'Corbis Entertainment Billing Worksheet.xlsx'!lookups,2,FALSE)), ""ENTER COGS"", VLOOKUP(A17,'Corbis Entertainment Billing Worksheet.xlsx'!lookups,2,FALSE)*D17))"
        
        End If
    
    End If

End Sub