Automatically update another cell

[krabople]

Hi, I have a spreadsheet with a table in it which shows a list of accounts. In column B, I have a field where the user can either select "Cancelled" or "Live" for the status of the account. In column D, I have a field whereby the user can enter the cancellation date. I was wondering if there is any way that if a user puts a cancellation date into a cell in column D, column B on the corresponding row will automatically update to "Cancelled". Under normal circumstances I would just have a formula in column B to check for this, however I was the user to be able to change this field manually if they need to, so obviously this is not viable. Any advice would be good.

[teylyn]

Hi,

you can have a formula in column B like

=IF(ISNUMBER(D2),"Cancelled","Live")

copy that formula down. After that, select the whole column B and go Data - Validation. on the Settings tab select Allow = List, Source =

Cancelled,Live

and hit OK.

Now the user can manually select a value in each cell, but keep in mind that when they do that, the original formula will be overwritten. It can be restored by copying and pasting from another cell in column B, but the user may not be aware of whether a Status of "Live" is really correct.

In order to show inconsistencies, you could use conditional formatting to highlight the rows where a cancellation date has been entered, but the status is still live, and rows where the status is Cancelled but no cancellation date has been entered.

To do this, again highlight the data in column B, then go Format - conditional formatting.

For the first condition select Formula Is and enter

=AND(ISNUMBER($D2),$B2="Live")

set a format for this condition and add another one, again Formula Is

=AND(ISBLANK($D2),$B2="Cancelled")

and set a format for this condition.

Now you can have your cake and eat it. Status will automatically be set when a cancellation date is entered. Status can be overwritten manually, but only with the two valid statuses. Inconsistencies in the data are highlighted, so data entry errors or issues can be spotted.

see attached for details

cheers

[krabople]

Hi Teylyn, many thanks for your reply. I'd thought of doing things similar to that myself, but in the end I still don't like the fact that the formula would be overwritten when the field is manually updated. I know this could just be copied down again, but the people who will be using this spreadsheet are not very Excel-savvy at all so I want to make it as fool-proof as possible. Do you know of any way I can do this with VBA?