Get words based off a conditioning format colored cell

Please help

I have a status column that is already created to generate a color based on what is presenting in certain columns. I have the colors red for denied, green for approved, and yellow for pending. I want to have another column that is the status words of approved, pending, or denied based on the conditioning formatting colors. Any ideas about maybe an IF statement?

I already tried making the status column generate pending, approved, or denied based off all the required columns and it didn't work which is why i used the conditioning formatting to get the colors at least as a visual. Now I need the words though in order to send a notification email when approved in the end so I am creating a new column with words based off the status conditioning format cells.

Genevieve P.

Hi @Please help

Any time you reference a column name with spaces in it, or numbers, you'll need to wrap the name in square brackets, like so:

[Column Name]

See: Create a Cell or Column Reference in a Formula. This means when you're referencing the "Requested Level of Release" cell, it should be written like this:

[Requested Level of Release]@row

As an alternative, you can click on the cell in the column as you're typing the formula to have it auto-populate with the correct syntax.

=IF([Requested Level of Release]@row = "Level 2", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined"), "Declined", "Pending")), IF([Requested Level of Release]@row  = "Level 3", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved", [Senior Medical Leader Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined", [Senior Medical Leader Approval]@row = "Declined"), "Declined", "Pending")))

Let me know if this one worked for you!

Cheers,

Genevieve

Genevieve P.

Hi @Please help

Can you explain what your conditions are for the Conditional Formatting rules? A screen capture of your condition rules would be very helpful!

We can definitely use an IF statement to populate words instead of (or as well as) the colours, but the formula will be based off of the same criteria as your Formatting, not the formatting itself.

An alternate way to get these words into your sheet would be to use the Change Cell workflow, if that would help. See: Change the Value of a Cell in an Automated Workflow

Cheers,

Genevieve

Please help

Hi @Genevieve P.,

So i need two different things here because there is a level 2 and a level 3 (i have a column named level for where the level numbers are as a drop down).

for level 2 i need if both the admin director and the medical director approve I need the status column to say complete AND be green. If only the admin director has approved i need it to say pending AND be yellow. Finally, if either the admin director declines or if the admin director approved but the medical director declines i need the status to read declined AND be red.

for level 3 i need the admin director, medical director, and senior medical leader to approve to be complete and read as green. IF only the admin director has approved out of the three so far or even if the admin director and the medical director have approved but the senior medical director has not submitted anything yet then i need it to read pending and be yellow for both situations of pending. Lastly, if any of the three people say denied whether it be the admin director, the medical director, or the senior medical leader even if the person before them approved then i need it to say denied and be red.

Genevieve P.

Hi @Please help

No problem! So we'll need to use two nested IF statements. The first thing is to check if your Level column is "Level 2" or "Level 3". Then depending on which level, we'll write the rest of the criteria.

Let's start with Level 2:

=IF(Level@row = "Level 2",

IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved"), "Complete",

IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined"), "Declined",

otherwise... "Pending"

^ Note that we have no instruction for Pending other than it's not both Approved or there are no Declined values. This does mean that if both of your approval cells are blank or some other value, it will show "Pending" - is that correct?

If so, your first statement could be as follows:

=IF(Level@row = "Level 2", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined"), "Declined", "Pending")), OTHERWISE....

Now we write the second statement. If Level is "Level 3":

IF(Level@row = "Level 3",

IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved", [Senior Medical Leader Approval]@row = "Approved"), "Complete",

IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined", [Senior Medical Leader Approval]@row = "Declined"), "Declined",

otherwise... "Pending"

Again, "Pending" will show as long as there are no "Declined" cells or if all three aren't "Approved". This means even if all 3 cells are blank it will show "Pending".

Put it all together:

=IF(Level@row = "Level 2", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined"), "Declined", "Pending")), IF(Level@row = "Level 3", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved", [Senior Medical Leader Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined", [Senior Medical Leader Approval]@row = "Declined"), "Declined", "Pending")))

Then you can base your Conditional Formatting off of the value that the formula inputs. Let me know if this works for you or if any of the values are appearing incorrectly!

Cheers,

Genevieve

Please help

@Genevieve P.

it did not work when i combined the two equations together. can you please use Requested Level of Release for the Level@row. Not sure why this is not working

Genevieve P.

Hi @Please help

Any time you reference a column name with spaces in it, or numbers, you'll need to wrap the name in square brackets, like so:

[Column Name]

See: Create a Cell or Column Reference in a Formula. This means when you're referencing the "Requested Level of Release" cell, it should be written like this:

[Requested Level of Release]@row

As an alternative, you can click on the cell in the column as you're typing the formula to have it auto-populate with the correct syntax.

=IF([Requested Level of Release]@row = "Level 2", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined"), "Declined", "Pending")), IF([Requested Level of Release]@row  = "Level 3", IF(AND([Admin Director Approval]@row = "Approved", [Medical Director Approval]@row = "Approved", [Senior Medical Leader Approval]@row = "Approved"), "Complete", IF(OR([Admin Director Approval]@row = "Declined", [Medical Director Approval]@row = "Declined", [Senior Medical Leader Approval]@row = "Declined"), "Declined", "Pending")))

Let me know if this one worked for you!

Cheers,

Genevieve