Turning flag red with conditional formatting

Tryg Kruger

Is it possible to turn a flag red using conditional formatting? I can turn the cell background red (or any other color), but not just the flag.

Atus Bartal

Tryg,

it's not possible with conditional formatting, but you can use a formula for setting it.

This is because "Flagged" and "Not Flagged" are not formats of a cell but its values. 

You may use an =IF() formula to set a value of a cell on specific conditions. 

Just an example: 

=IF(Checkbox6 = 1, 1, 0)  or simply =IF(Checkbox6, 1, 0)   [in a "Flag" Symbol type column - it also works with "Star" symbols or Checkboxes, because the value in these cells are either 0 or 1. If the cell in Checkbox column in row #6 is checked, the Flag or Star will be "on" (1), otherwise "off" (0).] 

 

I hope it helps.

Atus

 

 

Tryg Kruger

 

 

 

 

Well, not exactly, but thanks for your help. The dilemma I'm having is that I have a formula that sets the flag red if a date is today or in the past and a “Done” checkbox is not checked, but it also sets the flag red if the date field is blank, so I get a report with a lot of rows that aren't actually past due, which screws up my statistical reporting. I’ve spent a couple of hours trying to juke the formula, but haven’t succeeded yet. A reply to a Smartsheet blog by Kelly Anthony claims that one can use conditional formatting to effect the result I’m looking for, but as you’ve pointed out, it doesn’t work. If you, or someone, have any ideas on how to suppress the unwanted red flags, please help.

 

Atus Bartal

Thanks for the clarification.

I'd put an ISDATE() formula that gives you 1 if there is a (date) value in the date type column and 0 if it's blank. 

Peter Kirkham

Tyrg, you can absolutely do this. Just need to nest quite a few formulae to achieve your desired outcome. For example I set a flag based on whether an activity is supposed to have started, but has 0% progress, or is supposed to have finished, but has < 100% progress. Formula is (based on row 32):

 

=IF(Duration1 > 0, IF(OR(AND(TODAY() > [End Date]1, [% Complete]1 < 1), AND(TODAY() > [Start Date]1, TODAY() < [End Date]1, [% Complete]1 = 0)), 1, 0), 0)

 

Once the flag is set, you can then use conditional formatting based on the flag. In my case I have set up to use red font and red gantt bars if the flag is set.

 

In your IF statements, include an AND with the ISDATE formula as Atus suggests and it should work out well.

 

 

Ryan H

Can someone explain where I'm going wrong? Not an expert by any means, but can do some basic formulas. I'm trying to create a formula similar to Peter's (above), but don't need it to flag when something hasn't started. I only need it flagged if it's past the due date (Finish in my sheet), and less than 100% complete. Here's the formula I am using and I've tried several combinations, but still unparseable...

=IF(Finish20 < TODAY() AND([% Complete]20 < 1), 1, 0)

This should be simpler than I am making it. Any help would be appreciated!

Peter Kirkham

Ryan,

You need to nest the formulae correctly.

AND(Condition_A, Condition_B)

IF(Condition, Result_If_True, Result_If_False)

Nests as follows:

IF(AND(Finish20 < TODAY(), [% Complete]20 < 1), 1, 0)

You aren't using the AND formula correctly.

Peter

Nat Kenworthy

Hi there I am new to Smartsheet. I've learned how to write basic IF() formulas successfully.

Now I am trying to create a formula to turn a flag on if the checkbox column is unchecked.  Also, I only want the flag to turn on, on or after a given date. 

The flag type column is called Arrived?  And checkbox column is called PM: Arrived on Site. And the Date column is called Expected Delivery. 

I understand that I will need to write a relatively long correctly nested IF() formula, possibly combining an ISBOOLEAN function in it? I've also read that I'll need to be careful that that formula doesn't allow the flag to turn on up until given date but an ISDATE formula should cover that. 

How do I use ISDATE in this formula to make sure flag on switches on when Expected Delivery date has passed?

I can get the flag cell to read flag or don't flag based on checkbox on or off using =IF([PM: Arrived on Site]2 = 0, "Flag", "Dont Flag") but I don't want text, I want to use the flag symbol. What is the term needed in this formula to switch flag on or off?

I'd be really glad to receive any help you can offer. 

Thanks, 

Nathanael

 

J. Craig Williams

Nathaniel,

The easier part of the answer first:

To get a flag icon, you need to change your column type to Symbol. You do that through the Edit Column Properties dialog.

For the formula, I think this may give you what you want:

=IF(ISDATE([Expected Delivery]23), IF([Expected Delivery]23 > TODAY(), 0, IF([PM: Arrived on Site]23, 0, 1)), "Need Date")

for row 23.

blank Expected Date = "Need Date"

PM arrived on site = no flag (value = 0)

PM not arrived on site and due or past due = flag (value = 1)

I hope that helps.

Craig

 

JSmith

Hi 

I am trying to write a formula to update to the Column At Risk to true (red flag icon) when that particular rows Planned End Date has been exceeded and completion is not 100%.

 

The column At Risk which has a definition of type flag.

Screenshot is attached to illustrate the Smartsheets column structure.

 

I have written this formula, but am getting the error #UNPARSEABLE when I apply it.

=IF(Duration > 0, IF(OR(AND(TODAY() > [Planned End Date]3, [% Complete]3 < 1), AND(TODAY() > [Planned Start Date]3, TODAY() < [Planned End Date]3, [% Complete]3 = 0)), 1, 0), 0)

 

Can any one tell me what I am doing wrong?