Conditional Formatting Referencing Other Sheets

ChaunyT

Hello,

I'm building a process and want to add some automations to minimize errors. I have a column for "partner" that is a filled in via a form on sheet 1. I have a list of partners that we do not work with in sheet 2. I would like for the row (or cell) in sheet 1 to change colors if the form response is one of the names listed in sheet 2 (even better if it could just change a status without the color change being needed). Is this a functionality anyone else has managed to do?

I looked into cell linking and even some of the cell referencing options but I'm not seeing anything that quite matches up and would love to know if anyone has made this work.

Matt Johnson

Hi @ChaunyT

I think an INDEX MATCH formula would be one way to accomplish this. You could add a column on Sheet 1 for Partner Approved Yes/No (or something like that). You'd leave that field off the form. When the form is submitted the new column would look up the Partner name against sheet 2 and return maybe a check box or Yes/No status.

However, since its possible to enter a non-approved partner, I'm guessing you aren't using a dropdown for the Partner name on Form. You might want to consider a drop down showing only approved partners. Or have a dropdown with all partners, regardless of approval status, just to ensure the name gets entered correctly on sheet 1 so its compared accurately on sheet 2.

Here's an article on index/match in case you need it.

INDEX Function | Smartsheet Learning Center

https://help.smartsheet.com/function/index

Returns an item from a collection based on provided row and column indexes

I hope that helps.

Matt

ChaunyT

Hello Matt,

Okay, I tried the Index Match but adding the yes no column would be perfect! I'm going to try it out! Thanks for this!!