Change value to an alternative value Closed

Question

I am wanting the instructions to design a formula so that each time a value eg $90.00 appears in a cell that the system will automatically change the value from $90.00 to $100.00.

I am struggling with an =IF to accommodate this.

There will be three values that may appear so the formula would have to be :

if $90.00 appear it changes to $100.00, if $80.00 appears it changes to $85.00 and if $70.00 appears it changes to $60.00.  These values will appear in the same column within a range of around 20 cells.  Therefore from L4....L24.

Any assistance at all is so greatly appreciated.

TrowaD · Answer

Hi Megan,

You can not put a value and a formula in a single cell.

For that we need to create a macro.

Copy the code below, right-click on the sheets tab and select View Code.
Paste the code in the big white field, which you can then close.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("L4:L24")) Is Nothing Then Exit Sub
If Target.Value = 90 Then Target.Value = 100
If Target.Value = 80 Then Target.Value = 85
If Target.Value = 70 Then Target.Value = 60
End Sub

Back at Excel try entering the specified values in the specified range to see if it works as you foresaw.

Best regards,
Trowa

Change value to an alternative value

1 response

Similar discussions

Subscribe To Our Newsletter!