Conditional Formatting question

[KieranO]

Thanks for taking the time to read this thread i hope you enjoy your day

Basically I have a workbook that is for managing a fleet of pumps. Each pump has its own worksheet that details different pieces of information for each pump, one being its 'health'. The health cell is a drop down menu that list; Running, Attention required and out of service and ive used conditional formatting to colour each green, amber, red respectively.

Now my problem is, on the index worksheet at the beginning ive got all the pump listed and links to each of their worksheets and what im trying to do is copy the health from each pumps own worksheet to the index so i have a sort of 'dashboard' meaning if i opened the index i could see the health of all the pumps at once.

I've tried to do conditional formatting from each worksheet to the index however i think its having trouble because it doesnt see the values in the drop down box therefore it doesnt change colour.

Could anyone offer any advice and/or solutions for this?

Thanks again for reading,
Kieran

 

[Gerald Higgins]

Hi, welcome to the board.
It's difficult to be sure, but it sounds to me as if you need to set up two separate sets of Conditional Formatting.
One on each of the pump worksheets (if you want the colours to appear there).
And one on the index sheet.
In both cases, the CF should ideally be based on the contents of that actual sheet.

 

[KieranO]

Hi, welcome to the board.
It's difficult to be sure, but it sounds to me as if you need to set up two separate sets of Conditional Formatting.
One on each of the pump worksheets (if you want the colours to appear there).
And one on the index sheet.
In both cases, the CF should ideally be based on the contents of that actual sheet.

Thanks Gerald,

Yeah so that sounds correct, i have the CF on the pump sheet which works fine but trying to send that info to the index is where im having a problem but only with the list involved - if i ask the CF to look at a regular cell it works fine however its the drop down list that cause me a problem.

Thankyou for your response

 

[KieranO]

So, just in case anyone has a similar problem in the future heres the solution i thought of.

I come to the conclusion that you couldnt use conditional formatting on something that is already being conditional formatted. with that in mind, i used the IF function to look at the health cell and return either a "", 1, 2 depending on what was selected and display that in a unused cell. I then could use the conditional format on the index page to look at the 'IF' cell and return whatever colour i assigned to each outcome.

Maybe a bit of a lengthy work around or maybe theres a easier solution but ive found this worked for me and hopefully may help someone in the future

 

[Gerald Higgins]

Hi, you can indeed use CF on something that already has CF, in fact you can have multiple CFs applying to the same cell, and this is quite common.
If you want to do that, you have to be careful how you set it up, and in particular, specifying the order in which you want the CFs to apply - this can be quite tricky if you have lots of overlapping CFs.