I lose formula when number is entered in cell

Sean15

Well-known Member
Joined
Jun 25, 2005
Messages
698
Office Version
  1. 2010
Platform
  1. Windows
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top