07-30-2020 03:32 AM
07-30-2020 03:32 AM
Is there a way to implement 2 actions based on the results of an IF test? For example:
IF($G6=$K6,... for a true result I would like to implement both copying $G6 to $M6 and set $K6="".
I have tried IF($G6=$K6,$M6;$K6="") but it gives me an error.
Any possible suggestions?
07-30-2020 06:09 AMSolution
This is not possible with a formula. A formula returns a value in the cell that contains the formula; it cannot perform other actions. What you want requires VBA:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("G6,K6"), Target) Is Nothing Then If Range("G6").Value = Range("K6").Value Then Application.ScreenUpdating = False Application.EnableEvents = False Range("M6").Value = Range("G6").Value Range("K6").ClearContents Application.EnableEvents = True Application.ScreenUpdating = True End If End If End Sub
07-30-2020 06:09 AM
I believe what you can do is to type in the formula in M6, so that M6 gets the value of G6 when G6 is equal to K6.
In which case, this formula will suffice.
This assumes you want M6 value to be blank if G6 is not equal to K6.
However, the second aspect is dicey as I am not sure you intend to type in any formula in K6, given that K6 is part of the logical test in the IF test. In this case, you might need to use VBA to resolve this.
07-30-2020 06:54 AM
@HotBlue as already noted, a formula can only change the value of the cell it is in. Your desire to change K6 makes me think you might want to rethink how you have the worksheet set up. If K6 is a formula itself then you can just update that formula to include this condition. But if K6 is an input, it is better practice to not change it but rather separate input data and output / formatted display data so you have 'input data' on 1 page and then you can have the 'output data' on another sheet that is all formatted and adjusted the way you want it to look.