How to pass the "If" result to another cell

Question

I'm creating a business trip cost table with the following columns: "A" = Status ; "B" = planned costs (other columns doesn't matter now) Column "A" has drop down list with "planned, done, cancelled" values. Column "B" cells always has a number in it. (So in B column I can't write any function, because It will overwrite the price).

What I want is a criteria or code when the status in column "A" is changed to "cancelled", then the planned cost next to it becomes 0 or clears the cell.

I started to do this with a support sheet where I wrote a simple IF statement like this (in sheet2 random cell) =if(A2="cancelled"; B2=0; ""). But in this way it doesn't working. Simply gives a false result in to the random cell.

Any help would be appreciated.

If you can't type to column B, I don't think you will be able to overwrite it without VB. What you can do is, to add an extra column, and do something like `=IF($A2="cancelled"; B2*0; B2)` to show the updated costs. — Alex Szabo, Jan 14 '16 at 13:13

score 0 · Answer 1 · answered Jan 14 '16 at 13:07

0

=if(A2="cancelled"; B2=0; ""): couple of things

You are using semicolons instead of commas also you dont need to explicitly state B2 = 0 if this formula is going into the B column

answered Jan 14 '16 at 13:07

Jeanno

2,769
4
23
31

2

Semicolons and commas are defined by the client's regional settings. – Alex Szabo Jan 14 '16 at 13:11

score 0 · Answer 2 · answered Jan 14 '16 at 13:25

As already said: you cant change the cell without VBA. You would need something like this in the code-pane of the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Cells.Count = 1 And Target.Column = 1 Then
    If Target.Value = "cancelled" Then Target.Offset(0, 1).Value = 0
  End If
End Sub

Or do it (as also said before) in the "helper column" way with a formula like this:

=$B2*($A2<>"cancelled")

score 0 · Answer 3 · answered Jan 14 '16 at 13:28

0

=if(A2="cancelled",0,B2)

put this formula in C2 and C2 will display the result as you want.

answered Jan 14 '16 at 13:28

LazyRay

11
1

score 0 · Answer 4 · answered Jan 14 '16 at 13:31

0

You could create a third column(perhabs hidden) which hold the value now present in "B". Then use the function =IF(A2="cancelled";0;C2)in column "B".

answered Jan 14 '16 at 13:31

Cartoffel

35
6

score 0 · Accepted Answer · edited May 23 '17 at 11:52

I don't think you can do exactly what you asked for using just formulas in some other cells:

Making Excel functions affect 'other' cells

'=if(A2="cancelled"; B2=0; "")' Simply returns a logical value for "question" if B2 is equal to 0 if A2 happens to be cancelled. This is not what you want.

You could create a VBA code that would be executed each time something changes in your sheet.

automatically execute an Excel macro on a cell change

Press Alt+F11 then double click on the sheet you want the functionality and add the code

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Value = "cancelled" Then
    Target.Offset(0, 1) = 0
End If

End Sub

But I would strongly advise against using this solution, because now you would simply be overwriting whatever you have in cell in column B with value 0. Therefore you would lose your previous data in this cell.

What I would do:

1) If the data in column B is fed from somewhere else (not manually written) then I would write the function there, i.e. "=if(A2="cancelled";0; .get_original_value)"

2) If the input is manual I would add another column to keep "real value" there that would work like the function from point 1)

3) Or instead of 2) you can change only the display of the cells in column B based on values in column A by using conditional formatting You can start reading for example here http://www.excel-easy.com/data-analysis/conditional-formatting.html Most likely you calculate some aggregates (like sums) and want the cancelled value to be 0 for the sake of this calculations. By changing just the display the value would remain non-zero so instead of regular SUM you would have to use SUMIF and exclude the cancelled values, for example =SUMIF(A:A;"<>cancelled";B:B).

How to pass the "If" result to another cell

5 Answers5