Excel formula to change one cell value to another value based on a third cell

microsoft excelworksheet-function

to make this simple a1 has a value that can change, b1 has the formula to test a1 value. c1 is reset to a chosen value based on a1.

I have a table that people can input values. I have a list box in a1 which they can choose 'reset'. I want the table values to change to 0 , to 'clear' the table so people can then input new values.

I was trying to test this with a single cell as stated as above. The formula I put into b1

=if(a1="RESET",(c1=0),c1)

this isn't changing c1 value but is including the c1=0 into the test part of the if then.

trouble is we are not allowed to use Macros or vb code because where this is stored so I am forced to use formulas.

was this clear enough?

Best Answer

A formula can't change another target. You'll need two formulas

B1 - =IF(A1="RESET",1,0)

C1 - =IF(B1>0,"woohoo",0)

If the point of the exercise is to have only one formula in B1 because users can alter C1 to remove the formula, this isn't possible.

Related Solutions

Excel – Displaying a value in a cell dependent on another cell having text

I am not sure what you mean by "blank 'value'", whether that means it is actually blank or just showing as "0". But I plugged the provided information in Excel and it seems that your issue may be that C1 is showing as value "0", and so B1 is displayed? If so, you could try making C1 similar to B1:

=IF(Sheet1!E23="","",Sheet1!E23)

So if there is no value in Sheet1!E23, it will show as blank in C1, which will mean B1 will not populate according to the B1 formula.

Excel – how to either have direct data input or cell value from formula

As soon as a value is entered into a cell, any formula in that cell will be overwritten. You can use a VBA routine to restore the formula, though. Your requirements should include the logic for what has precendence, i.e. in what case should a formula be used and in what case does the manually entered number "win".

The following code works on these premises:

either cell A2 or A3 get changed
Both A2 and A3 must have values

So, when either A2 or A3 are edited AND both cells contain a value, the macro will write =A2+A3 into cell A4. If any of the values in A2 or A3 are text, the macro will still run and Excel will show the error message for adding text with numbers. Use Sum() to avoid the error message.

If cell A4 is then edited and the formula is overwritten, this value will show until either A2 or A3 are edited again.

If that does not match your requirements, please spell them out in more detail.

The code goes into the Sheet module (right-click the sheet tab, click "View code", paste code into the big code area.)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A2:A3")) Is Nothing Then
    If WorksheetFunction.Count(Range("A2:A3")) = 2 Then
        Range("A4").Formula = "=A2+A3"
    End If
End If

End Sub

Best Answer

Related Solutions

Excel – Displaying a value in a cell dependent on another cell having text

Excel – how to either have direct data input or cell value from formula

Related Question