If function to leave original value if not true

[charlesfarrell]

Hi Guys,


I am working on a formula that will update a cell value if two condictions are met, if the conditions are not met the cell value will remain the same.


For example, I have three columns, A, B and C. Each has values in the columns already, however I would like to change the value in Column C if criteria in A and B are met, if not to leave the original value. Is this possible and what might the formula be?


I have been using:


=IF((AND(A:A="BCJ",B:B="MEL")),"MEL",C:C)


When the criteria in A and B are met C updates with MEL, which is correct, however if not met it returns the value of 0, not the value originally in column C.


I'm not sure if I have explained this very clearly, happy to answer any questions. Thanks for the help everyon!


Charlie

 

[Cross Terrier]

You can't use a formula to leave the same value in a cell, unless that value was originally calculated by a formula: in which case that can then be made part of a formula to replace it.

It's not clear where your original values in columns A to C are coming from, but I would suggest hiding column C and then having a formula in column D that does what you require but shows the value in the hidden column C if the IF statement is false. If a column is hidden then you can still paste into it so it shouldn't affect the presentation too much.

So D1 has the formula =IF((AND(A1="BCJ",B1="MEL")),"MEL",C1)