# I lose formula when number is entered in cell

#### Sean15

##### Well-known Member
In H6, I have the formula below:

IF(H5="","",IF(C6="MATCH",H5*-0.25,""))

When the match criteria is met, Excel does calculation in H6.

When match criteria is not met, user enters number in H6.

This is all ok, but when worksheet is re-used, formula is lost in H6, H7, …

I’m assuming VB will be required. Could someone share VB codes or another way to fix problem?

Regards,

Sean

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Declamatory

##### Active Member
As soon as you enter the data in H6 you are going to override the formula.

Maybe you should have a helper columns. If the match criteria is not met get the user to enter in G6 instead.

The formula in H6 would then be IF(H5="","",IF(C6="MATCH",H5*-0.25,G6))

That way you can identify what needs to be input manually and you can clearly see from column G which fields have been input manually. Nobody should ever have to input into column H so the formula won't disappear.

#### oldbrewer

##### Well-known Member
the user is in effect deleting the formula in H6

get the user to enter number in I6

and make the formula =if(h5="","",if(c6="match",H5*-.25,I6))

#### Matt Rogers

##### Well-known Member
Hi.

You can park the formulas in the comments box.
However, it's strange ...

Macro:
Code:
``````Sub Formula_in_box()
Dim Used, z As Range
Set Used = Range(Cells(1, 1), Cells.SpecialCells(xlCellTypeLastCell))
For Each z In Used
If z.HasFormula Then
z.NoteText Text:=z.Formula
Else
z.NoteText Text:=""
End If
Next z
End Sub``````

#### Sean15

##### Well-known Member

@oldbrewer, @Declamatory - amended formula slightly to =if(h5="","",if(c6="match",H5*-.25,"")) because if circular reference in both.

@Matt Rogers, thank you.

Regards,

Sean

#### Declamatory

##### Active Member
There should be no circular reference. Without using VBA your formula hasn't changed from your initial one and you are still facing the same problem.

Using the formula IF(H5="","",IF(C6="MATCH",H5*-0.25,I6)) (where H5 ends up being blank input manually into I6) doesn't create a circular reference. The only way a circular reference would occur is if you were inputting the formula into H5 instead of H6.

Replies
25
Views
1K
Replies
0
Views
303
Replies
7
Views
361
Replies
3
Views
234
Replies
3
Views
274

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back