Conditional Formatting Triggered when Another Conditional Formatting is Triggered

[EvolutionJulie]

I've got a spreadsheet that helps me calculate the pkging costs for multiple clients. When a new pkging dimension is used in our shipping system that isn't listed in my spreadsheet, I've formulated my spreadsheet to turn that unknown box dimension's quantity to bright yellow until I can determine what the actual size of the box is, what it's cost is, and any notes that I need to make.

Here's where I need help. This spreadsheet is very wide normally - going out to about Column CC - and there's lots of dims (rows) listed. (I've only provided 1 client's worth of data.) I'm needing help doing a conditional formatting task on Cell L2. When any cell in Column B turns yellow, I'd like for Cell L2 to also turn yellow.

Now I know the easiest way to do this is to take the ISERROR part out of my formula in H2. Then my Cost in H2 would come back with an error if anything had turned yellow. Piece of cake. However, there are some dims in the purple table that need to stay as errors right now so I have to have the ISERROR part of that formula in H2.

If L2 could turn yellow when any qty in Column B turned yellow, then I'd be able to see much faster which clients have unknown dims that need to be addressed.

[TMS]

Try:

Type Name Formula
NR Cost =Table3[Cost]
NR Off_Road =Table3[Off-Road]
NR Off_Road_Cost =Table3[Off-Road Cost]

CF Formula (for cell L2) =COUNT(FILTER(Off_Road,(Off_Road<>0)*(ISERROR(Off_Road_Cost))))

I needed to create some Named Ranges that referred to the Table columns as the Conditional Format formula wasn't happy about Structured Table References.

[TMS]

OK, after a bit more research, you CAN use the Structured Table References but you need to enclose them with INDIRECT.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I don't know why you have to do that, but it works ... even though a) it makes the formula a lot longer and b) it is using a Volatile function.

[EvolutionJulie]

@tms - Thank you so much!!! The INDIRECT addition helped me!!!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.