Change text/ data in a cell based on cell colour

emily.quirk

Hi,

I am looking for a formula to change the text in a cell based on the cells' colour.

I have a formula in mind however it needs the numerical value associated with each colour.

hope you can help, thanks!

Christian Graf

Good News! @emily.quirk

Definitely Possible!

You won't even have to use workflows.

Here is a minimal formula with just Approve or Reject that you can put in a column formula for [Approval status].

=IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved", "Rejected")

Here is a formula for capturing blank values as well, you can make it a column formula in [Approval status].

=IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved", IF(ISBLANK([Operations Approved/Rejected]@row), "Waiting on Operations", IF(ISBLANK([Quality Approved/Rejected]@row), "Waiting on Quality", "Rejected")))

Paul Newcome

Yes. In this case I would also suggest a nested IF, but I personally would write it a little differently.

=IF(OR([Operations Approved/Rejected]@row = "Rejected", [Quality Approved/Rejected]@row = "Rejected"), "Rejected", IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved"))

Christian Graf

Hi @emily.quirk,

That is a great addition!

With nested IF conditions, you have to maintain the correct order. If something satisfies a condition first, it will go with the first option in the order.

For your application, we will need the "Waiting" status determination to occur before "Approved", or "Rejected".

For multiple statuses equaling the same thing "Waiting", I suggest using OR logic. So if anything is true in the OR function, it outputs the "Waiting". This cleans up your formula a bit by removing duplicate "code". Making it easier to troubleshoot and modify.

I like @Paul Newcome's example since it captures specific conditions and doesn't blanket statement any unforeseen entries as "Rejected".

So, using Paul's formula, and modifying your "waiting" statements, we get:

=IF(OR(ISBLANK([Quality Approved/ Rejected]@row), ISBLANK([Operations Approved/ Rejected]@row), ISBLANK([Engineering Approved/ Rejected]@row), ISBLANK([NPD Approved/ Rejected]@row), ISBLANK([Quality Feedback]@row), ISBLANK([Operations Feedback]@row), ISBLANK([Engineering Feedback]@row), ISBLANK([NPD Feedback]@row)), "Waiting", IF(OR([NPD Approved/ Rejected]@row = "Reject", [Engineering Approved/ Rejected]@row = "Reject", [Operations Approved/ Rejected]@row = "Reject", [Quality Approved/ Rejected]@row = "Reject"), "Rejected", IF(AND([NPD Approved/ Rejected]@row = "Approve", [Engineering Approved/ Rejected]@row = "Approve", [Operations Approved/ Rejected]@row = "Approve", [Quality Approved/ Rejected]@row = "Approve"), "Approved")))

Hope this helps!

Paul Newcome

Are you talking about the cell fill / background color? Are you able to provide some screenshots for context?

Christian Graf

Hello @emily.quirk,

Unfortunately this functionality does not exist in Smartsheet. There is no way to change the text in a cell based on the cells color alone.

However, there are several ways to work around this.

1) If you are manually changing the colors of the cells, then you can create a new column named [Color]. You can change it to a drop down list that contains all the options you'd like: (Red, Yellow, Green, etc.). When you select a color you can use conditional formatting to change the color of certain cells. You could then tie your formula to the [Color] Column.

2) If you wanted the colors to automatically change based on condition in your sheet, you can also create an automated workflow. This would change the value of your [Color] column if certain conditions are met. You could then hide the color column and your sheet would be automated in the background appearing to function the way you described, but in a round about manner.

Hope this helps!

emily.quirk

Hi @Paul Newcome, Yes I am talking about the Background/ fill colour

For example, if the fill colour is green I would like the text to change to 'Approved'.

If the fill colour is Red I would like the text to change to 'Rejected'

Kind regards,

Emily.

emily.quirk

https://community.smartsheet.com/discussion/comment/356189#Comment_356189

Hi Christian, Thanks for your response!

Could you give me some more information on option 2? Sounds like this would work but I am very new to smartsheet so will need some further direction!

Thanks,

Emily.

Christian Graf

Hi @emily.quirk,

I don't want to step on @Paul Newcome's toes, looks like he was here to help first, I didn't see his reply before I sent mine.

Like Paul said, I think more information on how your sheet is setup is needed. If you are able to copy the sheet and delete any sensitive information so all the column can be seen then post a picture that would be best, otherwise if you could describe the usage of the sheet and how you enter the information Paul or I can continue from there.

How are you changing the background color at this time? Do you change it manually? Or are you using conditional formatting? (Automatically changed color when something is entered in a different column)

Are there values entered in other columns that could be used to determine if the approval status is approved or rejected?

emily.quirk

Thank you both for your help, @Christian Graf , @Paul Newcome.

See below a snip of the worksheet.

The Approval Status, column 9; changes colour using conditional formatting. (If both 'Operations Approved/Rejected' and 'Quality Approved/Rejected' are accept, the Approval status cell turns green, if one or both are reject, the Approval status cell turns red.

What I would like is for the text to change also, as currently the cell colour could be red, but text could still say approved.

The Approval status determines the status of the entire project and will put the project at risk/ complete so it is important the text changes also.

Do you think this is possible?

Christian Graf

Good News! @emily.quirk

Definitely Possible!

You won't even have to use workflows.

Here is a minimal formula with just Approve or Reject that you can put in a column formula for [Approval status].

=IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved", "Rejected")

Here is a formula for capturing blank values as well, you can make it a column formula in [Approval status].

=IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved", IF(ISBLANK([Operations Approved/Rejected]@row), "Waiting on Operations", IF(ISBLANK([Quality Approved/Rejected]@row), "Waiting on Quality", "Rejected")))

emily.quirk

That's great news, thanks a mil, I will give it a try!!

Paul Newcome

Yes. In this case I would also suggest a nested IF, but I personally would write it a little differently.

=IF(OR([Operations Approved/Rejected]@row = "Rejected", [Quality Approved/Rejected]@row = "Rejected"), "Rejected", IF(AND([Operations Approved/Rejected]@row = "Approve", [Quality Approved/Rejected]@row = "Approve"), "Approved"))

emily.quirk

Hi @Christian Graf,

Thank you for all your help. Really didn't think I would get this resolved!

The formula you showed me is working perfectly! I tried to add extra sections so Approval status would be waiting until both 'feedback' and 'approval' is documented.

The sheet doesn't seem to recognise the last highlighted section. There is no error coming up however it is changing to approved once all approvals are added, even when no feedback documented.

=IF(AND([NPD Approved/ Rejected]@row = "Approve", [Engineering Approved/ Rejected]@row = "Approve", [Operations Approved/ Rejected]@row = "Approve", [Quality Approved/ Rejected]@row = "Approve"), "Approved", IF(ISBLANK([Quality Approved/ Rejected]@row), "Waiting", IF(ISBLANK([Operations Approved/ Rejected]@row), "Waiting", IF(ISBLANK([Engineering Approved/ Rejected]@row), "Waiting", IF(ISBLANK([NPD Approved/ Rejected]@row), "Waiting", IF(ISBLANK([Quality Feedback]@row), "Waiting", IF(ISBLANK([Operations Feedback]@row), "Waiting", IF(ISBLANK([Engineering Feedback]@row), "Waiting", IF(ISBLANK([NPD Feedback]@row), "Waiting", "Rejected")))))))))

For example, the second row should still say waiting as no feedback is documented.

I hope this makes sense...

Thanks in Advance,

Emily.

Christian Graf

Hi @emily.quirk,

That is a great addition!

With nested IF conditions, you have to maintain the correct order. If something satisfies a condition first, it will go with the first option in the order.

For your application, we will need the "Waiting" status determination to occur before "Approved", or "Rejected".

For multiple statuses equaling the same thing "Waiting", I suggest using OR logic. So if anything is true in the OR function, it outputs the "Waiting". This cleans up your formula a bit by removing duplicate "code". Making it easier to troubleshoot and modify.

I like @Paul Newcome's example since it captures specific conditions and doesn't blanket statement any unforeseen entries as "Rejected".

So, using Paul's formula, and modifying your "waiting" statements, we get:

=IF(OR(ISBLANK([Quality Approved/ Rejected]@row), ISBLANK([Operations Approved/ Rejected]@row), ISBLANK([Engineering Approved/ Rejected]@row), ISBLANK([NPD Approved/ Rejected]@row), ISBLANK([Quality Feedback]@row), ISBLANK([Operations Feedback]@row), ISBLANK([Engineering Feedback]@row), ISBLANK([NPD Feedback]@row)), "Waiting", IF(OR([NPD Approved/ Rejected]@row = "Reject", [Engineering Approved/ Rejected]@row = "Reject", [Operations Approved/ Rejected]@row = "Reject", [Quality Approved/ Rejected]@row = "Reject"), "Rejected", IF(AND([NPD Approved/ Rejected]@row = "Approve", [Engineering Approved/ Rejected]@row = "Approve", [Operations Approved/ Rejected]@row = "Approve", [Quality Approved/ Rejected]@row = "Approve"), "Approved")))

Hope this helps!