How to change 1 cell value based on the value of another cell

[Nelly3007]

Good Morning Everyone,

I have some conditional formatting set up to change the row to Grey if column F shows 'Cancelled' (just as '=$F1="CANCELLED" ') but was wondering if there is also a way to set it up so that if column F shows 'Cancelled' then column E changes to £0.00? Column E is manual data entry.

Many Thanks,

 

[Joe4]

Conditional Formatting can only change the formatting of cells, not their actual value.

A cell contain contain a hard-coded value, or a formula, but never both at the same time.
So to do what you want would require one of the following:
1. VBA to update the cell, as per your requirements. This may be able to be done automatically, if the value of "Cancelled" is being entered into column F manually by someone.
2. The use of an additional column. So you would leave column E the way that it is (maybe re-label it?), but then add an additional column, label it someone like "Real Cost", and enter a formula that looks at the values in your current columns E and F, and decides which value to return.

Formula in cell F2:

=IF(G2="Cancelled",0,E2)

If you would like to pursue the VBA option, please indicate how & when your "Received?" column is being updated.

 

[Nelly3007]

Conditional Formatting can only change the formatting of cells, not their actual value.

A cell contain contain a hard-coded value, or a formula, but never both at the same time.
So to do what you want would require one of the following:
1. VBA to update the cell, as per your requirements. This may be able to be done automatically, if the value of "Cancelled" is being entered into column F manually by someone.
2. The use of an additional column. So you would leave column E the way that it is (maybe re-label it?), but then add an additional column, label it someone like "Real Cost", and enter a formula that looks at the values in your current columns E and F, and decides which value to return.

View attachment 120880

Formula in cell F2:

=IF(G2="Cancelled",0,E2)

If you would like to pursue the VBA option, please indicate how & when your "Received?" column is being updated.

Good Morning Joe,

I have never tried VBA before and would prefer to stick to something I can 'maintain' myself so the extra column is great and has solved my issues. Thank you very much!

Many Thanks,

 

[Joe4]

You are welcome.
Glad I was able to help!