Conditional Formatting Over 2 worksheets

Good Afternoon

I have an issue with conditional formatting in that excel will not allow it to go over 2 worksheets in a workbook.

On one sheet i have a set of questions with Yes or no answers or tick or cross whichever can be entered.

On the next sheet i have the list of days Monday - friday

I want to be able to change the cell to Green if all questions are completed and red if it has not been completed or there is a No or x in the box.

Is this possiable. It also has to stay on seperate sheets.

Many Thanks

Originally Posted by NBVC

If you used named ranges in the reference sheet, then you can use conditional Formatting over the 2 sheets.

Thanks for your reply

Would you be able to explain that in idiot terms...I'm not too advanced in excel.

I have attached the file for you to view if this is easier for you to understand what im trying to do.

Many Thanks

Originally Posted by NBVC

So, in this workbook you attached, what should exactly be happening in Sheet2 and why?

In Sheet 2 if any of the questions are Blank or have an X i need the cell under monday to be Red

If all the cells for monday on sheet 1 have a tick mark then the cell needs to be green below monday.

Thanks

Originally Posted by NBVC

You have more than 1 Monday column in Sheet1.. do all need to be filled?

If it is only the first Monday column you are concerned with... questions 9 and 10 have no checks so should the Sheet2 Monday be red?

Sorry i have included the correct copy now.

I need one for each monday of the month as its a daily checklist. This version has correct questions with only tick or x answers

Originally Posted by NBVC

For this maybe it is just as easy to use an alternative to Named ranges...

We can use INDIRECT() to reference cells on other sheets.

See attached.

This is applied only to Monday in cell B2 of Sheet2

Do the same for each other cell, only changing the referenced ranges to the corresponding ranges on other sheet.

Alternatively, you can name each range on Sheet1, for example

G14:G29 would be named Mon1

H14:H29 would be named Tue1

etc

then your CF formulas would be

=Countif(Mon1,"ü")=15 ...green

and

=Countif(Mon1,"ü")<>15 ...red

Thanks again for the reply

I have entered the count if statement for the indirect but it stays red doesnt change to green

Originally Posted by jason_1

Thanks again for the reply

I have entered the count if statement for the indirect but it stays red doesnt change to green

Ok i sorted that issue

They have changed what they wanted me to do. They want to add a column into the end of the week with status and if any of the actions in the week have a X the status is Red

On the dails sheet it doesn't matter if it is a tick or a cross as long as the cells are filled with a ü (Tick) or a cross the cell should be green.

Sorry about this, i did try to change it but couldn't work out how

Originally Posted by jason_1

Ok i sorted that issue

They have changed what they wanted me to do. They want to add a column into the end of the week with status and if any of the actions in the week have a X the status is Red

On the dails sheet it doesn't matter if it is a tick or a cross as long as the cells are filled with a ü (Tick) or a cross the cell should be green.

Sorry about this, i did try to change it but couldn't work out how

The Only issue i can see is if there are all ticks for monday the status for the week would be green, but if you use the count cells option which would be 45 cells to count it would show the status of the week as red as the other cells were blank.

AHH confusing stuff

Originally Posted by NBVC

Post a new sample workbook and show some expected results.

I have now attached the updated copy. sorry for the delay i have been out to dinner after work.

What i need it to do is to ignore the day until it is filled in so it stays clear.

With the status for the week, i need it to be green if none of the boxes for the week have a x but to change to red if there is an x that week, but need it to ignore days that arn't filled in or it will be false reporting issues when there are none.

Hopefuly that makes sense.

Ill try to explain this better as it confused me whn i was asked about it.

Each day a member of staff with tick or x the questions. On sheet 2 i need it to only show green if the day has been completed regardless of what is entered into the box (Tick or X)

The status of the week is to show issues when an X is entered into the sheet. So if Monday was all ticks the status would be green. If tuesday was all ticks then it would be green still. If there was a question on thursday that was answered with an X then the status would then turn red. then friday if it was all ticks again the status is still red.

Originally Posted by NBVC

So we are just looking at cell G2, "status"

Then for red:

=COUNTIF(INDIRECT("PSE!$G$14:$K$22"),"X")>0

and for green:

=COUNTIF(INDIRECT("PSE!$G$14:$K$22"),"X")=0

Ah that seems to work great

How do i get the monday to friday to go green after all questions are entered?