I lose formula when number is entered in cell

[Sean15]

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

 

[Declamatory]

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]

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]

Hi.

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

Macro:

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]

Thank you for your suggestions.

@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]

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.