conditional formatting between worksheets

I have a holiday tracker and i want a summary sheet on the first page and then the employees on each tab. the layouts are all the same and this is the formula in the employee section:

COUNTIFS($A$42:$A$61,"<+"&F6,$B$42:$B$61,">="&F6)

I want this formula to work on the summary tab as well but take the dates from the worksheet labelled Tom. i have tried adding TOM! inbto the formula, but it keeps saying that i cannot use condtional formatting between worksheets.

I have attached my spreadsheet.

Give the Holidays Date To range a name, for example, TomTo. You've already given a range name to Holidays Date From range (Tom).

Then use those range names in your conditional formatting:

Please Login or Register  to view this content.

Regards

hi guys,

i have tried this. i have named the ranges and used the formula on the summary page, but only the Tom link works and it doesnt on the new pages i have added.see if you can spot where i am going wrong.

the range on the summary page is F6:F37 TO AB6:AB37

the formula i am using is

=COUNTIFS(TOM,"<="&F6,TOMTO,">="&F6) to make it red
=COUNTIFS(****,"<="&F6,DICKTO,">="&F6) to make it blue
=COUNTIFS(SALLY,"<="&F6,SALLYTO,">="&F6) to make it purple

I have named all the ranges on each of their page.

Sorry guys its working now!! Thanks for all your help!!

That's good news. Thanks for the feedback. What did you do to make it work?

Can you mark your post as "solved"?

Regards