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?

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 in the background?

I have this formula in cell 1 (although it needs to be changed according to my requirement I state in this question):

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

In cell 2, I only enter a number like 79 or 79.5 for example.

So first priority is that I can enter a value (for example 89 or 89.5) in cell 1 while the formula is protected in the background.
Second priority is that if cell 1 is empty (the formula still there, so it's really not 'empty') the value from cell 2 should be retrieved to cell 1 while the formula is protected in the background.
Third priority is that if cell 2 is also empty no error message or anything should be written in cell 2 ("iferror").
LVL 1
hermesalphaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try

=IF(T5<>"";T5;IF(W5="";"";W5))
hermesalphaAuthor Commented:
I tried your solution Saqib, but I get a value error for it when cell 2 is empty.
Saqib Husain, SyedEngineerCommented:
I do not get that error. Can you upload an excel workbook showing this?
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Ejgil HedegaardCommented:
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

Open in new window

hermesalphaAuthor Commented:
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).
hermesalphaAuthor Commented:
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.
hermesalphaAuthor Commented:
Ok, I just double-clicked on the worksheet module for sheet 1. I suppose it's here I should enter your code then.
hermesalphaAuthor Commented:
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.)
Ejgil HedegaardCommented:
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

Open in new window

hermesalphaAuthor Commented:
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"
Ejgil HedegaardCommented:
The macros don't have to be activated manually to run.
The event macros start by the events worksheet calculation and worksheet change.

Macro names can not have spaces, but you don't have to create names.
The names must be exactly as they are in order to work.
And must be in the worksheet module they have to work for.

When you open a workbook with macros, a message appears between the worksheet and the ribbon.
Use that to activate macros.
It is possible to enable all macros, and avoid the message (Excel settings, security).
Microsoft recommend not to, but it is also annoying to do it all the time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hermesalphaAuthor Commented:
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.
hermesalphaAuthor Commented:
Excellent, thanks!
hermesalphaAuthor Commented:
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.
Ejgil HedegaardCommented:
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

Open in new window

hermesalphaAuthor Commented:
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
hermesalphaAuthor Commented:
I got it to work now, just changed from Change to Calculate.
Ejgil HedegaardCommented:
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
hermesalphaAuthor Commented:
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).
hermesalphaAuthor Commented:
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.
Ejgil HedegaardCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.