How do I retrieve a value from cell 2 to cell 1 if cell 2 contains a value and cell 1 is empty, but prioritize to let a value be input in cell 2 while the formula in cell 2 is protected?

Try

=IF(T5<>"";T5;IF(W5="";"";W5))

I tried your solution Saqib, but I get a value error for it when cell 2 is empty.

I do not get that error. Can you upload an excel workbook showing this?

It is not possible to do what you want by formulas.
A cell can have a value or a formula, not both.
A formula can not be protected in the background.
When you type a value in the cell, the formula is lost.
The "correct" solution is to have input cells for the values, and result cells with formulas.

With VBA it is possible to restore the formula when the cell value is deleted.
It could be like this  (code in the worksheet module), using A1 for the cell with the formula.
The formula here is:
If A2 is not empty (or an empty string) use the value in A2, else display an empty string.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing And Target.Count = 1 Then
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.Formula = "=IF(A2<>"""",A2,"""")"
            End If
        Application.EnableEvents = True
    End If
End Sub

Select allOpen in new window

Ejgil, I think I was a bit unclear, this is a clarification below. Will your VBA still be valid?:

S28 contains this formula: =IFERROR(S27+S10+S11+S12+S13+S14+S15+S21+S22;S27)
S29 contains this formula: =IFERROR(S28;"")

If cell S28 is empty (or rather no value there, formula is still there), also leave S29 empty (no error message).
If cell S28 contains a value, copy this value to S29.
I might want to modify this copied value, and do that. Then I might want to go back to the value that was retreived from S28 instead, so I want the formula to run again (restore the formula when the cell value is deleted).

Where do I go in the VBA Editor? I open Modules/Module1 and choose Insert/Module and get to the VBA Editor. Is this the place I should enter the code?

I tried Microsoft Excel Objects and chose the sheet I am working with, but I can't insert any module here.

Ok, I just double-clicked on the worksheet module for sheet 1. I suppose it's here I should enter your code then.

But after having entered your code, how do I run/activate it so it's valid for the worksheet?

I tried now to close the VBA Editor and return to the worksheet, but it didn't work, the formula was deleted when I overwrote S29 with a value.

(Instead of A1 and A2, I replaced with S28 and S29.)

Macros must be activated, then it will run automatically.
To restore the formula in S29, S29 must be compared to Target
Intersect(Target, Range("S29"))
And it must be the formula for S29 that is used
Target.Formula = "=IFERROR(S28,"""")"
So when you delete S29, the formula is inserted again.

I think the following code does what you want.
Set value in S29 to value in S28 when S28 change as result of calculation, and S29 has a formula.
If S29 has a value nothing happens.
When S29 is deleted, the formula is restored.
Replace all existing code in the worksheet module.

Option Explicit
Dim S28Value As Variant

Private Sub Worksheet_Calculate()
    If S28Value <> Range("S28") And Range("S29").HasFormula = True Then
        Application.EnableEvents = False
            Range("S29") = Range("S28")
        Application.EnableEvents = True
    End If
    S28Value = Range("S28")
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("S29")) Is Nothing And Target.Count = 1 Then
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.Formula = "=IFERROR(S28,"""")"
            End If
        Application.EnableEvents = True
    End If
End Sub

Select allOpen in new window

I tried now (after I had replaced with your new code) to run it, and a small window popped up asking for Macro name. I entered a macro name (with spaces between, is that ok?) and clicked on "Create" (the option "Run" was in grey, not possible to select). But then I got this error message in a small popup window:

"Sub or function not defined"

Great Ejgil, it worked!. I had not selected "Worksheet" in the editor window but "(General)". After I changed it worked perfect: If there is a value in cell S28 then S29 retrieves that value, if not it stays empty without error message, and if I change the value retrieved from S28 it changes to my new value. And then if I delete this changed value the value from S28 is retrieved. The formula gets restored after I have pressed Delete in the cell.

Excellent, thanks!

Just wonder if I want to extend the range to P28:W28 and P29:W29, which changes do I need to make in your code? I thought it was only to replace with P28:W28 and P29:W29 where appropriate, but in some places (like "S28Value") it does not seem to work doing so.

Expanding to ranges is not that simple.
S28Value holds the previous value for S28, to compare with on the next calculation.
The variable is for one value only, and is not designed to have all 8 values for the range P28:W28.

Try the code below for the range P28:W29.
If the value in a cell in P28:W28 change, and the cell below in P29:W29 has a formula, the value in the cell replace the formula.
Restore of the formula is done one by one, so when you delete W29 only W29 get the formula restored.
I have added error trapping to be sure that EnableEvents are set again if an error occurs, which is more likely the more code there is.
It does not happen automatically when the sub ends.

Option Explicit
Dim KeepValue(1 To 8) As Variant

Private Sub Worksheet_Calculate()
    Dim i As Integer
    On Error GoTo ErrorOut
    Application.EnableEvents = False
    For i = 1 To 8
        If KeepValue(i) <> Cells(28, i + 15) And Cells(29, i + 15).HasFormula = True Then
            Cells(29, i + 15) = Cells(28, i + 15)
        End If
        KeepValue(i) = Cells(28, i + 15)
    Next i
ErrorOut:
    Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("P29:W29")) Is Nothing And Target.Count = 1 Then
        On Error GoTo ErrorOut
        Application.EnableEvents = False
            If IsEmpty(Target) Then
                Target.FormulaR1C1 = "=IFERROR(R[-1]C,"""")"
            End If
ErrorOut:
        Application.EnableEvents = True
    End If
End Sub

Select allOpen in new window

Thanks, I tried your latest code now. But I get prompted each time I try to run it to enter a macro name (I have enabled always run macros without prompting). Do you think it can have anything to do with what they discuss here?:

http://stackoverflow.com/questions/18639581/why-does-excel-vba-prompt-me-for-a-macro-name-when-i-press-run-sub

I got it to work now, just changed from Change to Calculate.

That is a possibility, that somehow events have been disabled.

I don't understand what you are doing.
You don't need to change anything.
When you copy the entire code to the worksheet module, replacing everything there is, it should start automatically.

And you can't have 2 Worksheet_Calculate subs in the same module.

Try attached file.
When you open it, a message box should appear saying "Events are enabled".
If not run the sub ReEnable_Events() in the worksheet module.
Then you can change the cells on the sheet, and see what is happening.
Restore-formula.xlsm

Ejgil, is it possible to change the formula so that this subroutine runs for all cells in the whole workbook? So that if there is a formula in a cell, it gets restored if something first is entered in a cell and then this input is deleted?

Also, I should be able to enter a new formula in any cell. But the main thing is: I should be able to overwrite a formula with anything in any cell, but if I delete this contents and the cell is empty the old formula should be restored.

Or maybe it's not feasible to have it for the whole workbook because if I would make small adjustments like moving areas etc. the old formulas would be retrieved and I would loose control (would get formulas in cells I loose control over).

If I can't have this subroutine for the whole workbook (or if it would not be recommendable), how can I add more rows to be included in the subroutine?

It's like a table, and I will have this table 14 more times on the same worksheet, having totally 15 exactly the same copies of this table below each other, so the columns are always the same, only need to add 14 x 2 more rows in the subroutine.

You set up 2 scenarios.

The second is expanding the compare solution to have more ranges.
It has a specific defined formula to restore.
This will work even if the cells has a value at start.

Here is a test with 3 blocks.
There are 2 named ranges, CompareRange and RestoreRange.
CompareRange =Sheet1!$P$28:$W$28,Sheet1!$P$50:$W$50,Sheet1!$P$72:$W$72
RestoreRange =Sheet1!$P$29:$W$29,Sheet1!$P$51:$W$51,Sheet1!$P$73:$W$73
RestoreRange is the rows below CompareRange, and that is the cells where the formula is restored when cell value is deleted.

Go to Names on the Formula tab to add more ranges to the names.
Select the name and the ranges are shown in the reference box at the bottom.
Use the icon to the right of the reference box and define all ranges by pointing.
Select the first part of the range.
Hold down Ctrl key and select the other parts with the mouse.
The Ctrl key can be released between selections, but if you forget to press again at next selection, all is lost and you have to select once more.
When all is selected go back to the names definitions (use the icon to the right), use the acceptance icon to the left, or press Enter and accept the warning.

The first scenario is to restore any formula.
I have incorporated that in the same workbook.
The formulas are read at first selection change, and it takes a few seconds to initialize.
Later changes works much faster.
VBA knows the last cell used of the sheet, and here that is Z73, so all formulas from A1 to Z73 are remembered.
If something is added to the right or below that range, the remembered range is expanded.
You can also copy formulas.

But if you insert or delete rows or columns it can be messed up, since the original formulas are linked to the original rows and columns.
Try delete column Y where there is a sum formula in Y10.
The formula will be restored in Y10, since Z10 is empty, but then the value in Z9 is in the same column as the formula.
So to make major changes to the sheet set up, open the workbook without macros enabled, make the changes, save and reopen with macros.

The scenarios you set up are interesting to explore how to make, but I think this question has grown way beyond the initial one with just one cell.
Restore-formula-2.xlsm