Conditional Formatting between 2 different Sheets

TaliRXM

Hello,

I am looking for an option to have a conditioning rule in one sheet and once changes, it triggers a format change in another sheet?

I saw this question came up in 2016 and the support team said they will pass on as an enhancement request.

Can be done now?

Thanks!

Genevieve P.

Charts can only be displayed on a Dashboard using the Chart Widget. Here's a free Webinar that goes through how to build a Dashboard!

SmartStart: Dashboards

Genevieve P.

Hi @TaliRXM

Are you able to explain your specific scenario a little further?

It's likely we can build something out using a cross-sheet formula that could check a box in a second sheet if your specific conditions are met on the initial/original sheet, then apply conditional formatting rules to the row where the box is checked. However in this instance we would need to have some sort of unique identifier in each row so that the formula can match rows/conditions across sheets. Does that make sense?

If you'd like help building this out, it would be useful to see a screen capture of both sheets and the Conditional Formatting rule that you want applied, but please block out sensitive data.

An alternative to this would be to create a Report instead of using a second sheet. Reports will pull through the formatting on the underlying sheet, so this way as the formatting changes it will automatically adjust the Report's formatting.

Cheers!

Genevieve

TaliRXM

Thanks Genevieve for the reply.

Here is what I like to do:

1) as soon as a cell changed manually on the 1st sheet, and highlighted to Green

2) it will trigger the same change on a cell on another sheet - will be highlighted as well

Is it doable?

Thanks again!

Genevieve P.

Hi @TaliRXM

Those two cells contain very different values. What links the rows across both sheets? For example, is it the same task name in both sheets? Or an ID? How do you know that "Manuscript" is associated with "Jun-22"?

TaliRXM

@Genevieve P. Hi, it is the same item. In one sheet there is a date associated with when received (then it is turned to a green highlighted cell - manually). Then we like the other cell in the other sheet to be turned to 'green highlighted' (the same meaning = the item received).

Thanks!

Genevieve P.

Hi @TaliRXM

Is the Item Name unique? Is there a column for the Item Name across both sheets?

If so, we can use a cross-sheet formula that looks to see if there is a Date in your first sheet that matches the same Item Name row, and if there is, check a box.

This is an example formula that could do this:

=IF(COUNTIFS({Item Name Column}, [Item Name]@row, {Date Column}, <> "") > 0, 1, 0)

Then you would set up a Conditional Formatting rule where if the box is checked it turns the cell green.

If you need help building the formula, I would need to see full screen captures of both sheets to identify what column to look at, but please block out sensitive data.

Cheers,

Genevieve

TaliRXM

The item name is not exactly the same.

I will look and explore your suggestion.

Thanks for all the help!

Tali

Genevieve P.

No problem at all! It sounds like there would need to be more consistency across both sheets before this type of solution would work for you.

If you come across any other questions as you build this out, feel free to post a new Question and the Community will be happy to help further. 🙂

TaliRXM

Will do Thanks.

I have another question: can a pie or other chart be build only from a Dashboard or is there an option to reflect information in a report using charts?

Genevieve P.

Charts can only be displayed on a Dashboard using the Chart Widget. Here's a free Webinar that goes through how to build a Dashboard!

SmartStart: Dashboards

TaliRXM

Thank you!