Conditional Format: Change Text if Other Cell Not Blank

[PostTIT]

As the title says I'm wanting conditional formatting to be in use.
Column K:K contains cells that are to be manually changed from blank to not-blank.
When the cell is not blank I want to cell beside it, in Column L:L, to change text from "Due" to "Complete".

Is this possible? I've managed to get it to change format of the cell via: =NOT(ISBLANK(K1) but I need to change the text within the cell, not the formatting, as other cells/formulae are reliant on it changing.

 

[Joe4]

Changing values is not Conditional Formatting. Conditional Formatting has to do with just that, the format of cells (color, font, shading, underlining, etc).

It sounds like what you want is a formula, something like the following in cell L1 (and copied down):

=IF(K1="","Due","Complete")

 

[arthurbr]

You cannot use CF to change the text in a cell.
A formula like =IF(K1<>"","complete","due")
Pull down as needed

 

[Special-K99]

Cells cannot change the values of other cells, you need VBA for this.
You can however set the value of a cell based on another cell.
So you could have a formula in L1
=IF(K1<>"","Complete","Due")

 

[frank_AL]

Not sure you can do that with Conditional Formatting but why not use an If statement?

Copy this formula into Cell L2 and then copy down for all rows you want to evaluate: =IF(ISBLANK(K2),"Due","Complete")

 

[arthurbr]

Be aware that ISBLANK only works on an empty cell. If it contains the "" null string, results will be incorrect.
This does work, however with COUNTBLANK

 

[PostTIT]

Thanks all. I was aware of using the IF statement and having it work that way, however I've basically made this not an option with the way I've set up my spreadsheet...

I have two sheets, the sheet that I'm concerned with (sheet two) has a table on it with lots of data and the IF statement would work great if it wasn't for how the data gets added to the table:

The data is entered on sheet one in a one-row version of the same table, the user clicks a button and a macro copies the data to the table on sheet 2 in the newly inserted first row (so that it's newest item first). The table on sheet two can then be changed manually when needed by the user, and it's at that point - when the user puts in a completed date in the relevant cell - I was wanting the cells' text to change from "Due" to "Complete". So the cell already has the word "Due" in it and wouldn't be able to contain the IF statement...

Any way around this? Or does it have to be manually done?

 

[Joe4]

For this to happen automatically will require VBA.
Right-click on the sheet tab name at the bottom of your sheet, select View Code, and paste this code into the resulting code window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if single value in column K updated
    If Target.Count = 1 And Target.Column = 11 Then
'       If column K is populated and column L equals "Due", change to "Complete"
        If Target <> "" And Target.Offset(0, 1) = "Due" Then
            Target.Offset(0, 1) = "Complete"
        End If
    End If
    
End Sub

This should do what you want.

 

[PostTIT]

You're the man! Thanks.

Funny how such a small amount of code takes ages to learn. Practice here I come.

 

[Joe4]

You are welcome!

The key is that it is Event Procedure VBA code. Event Procedures are VBA code that run automatically upon some "event" happening (like the changing of a cell).
There is a good write-up on Event Procedures here: Events In Excel VBA