How do i get cells in a calendar to highlight based off information from another sheet.

I have a Tracker Calendar i got from another thread that i was able to adjust to my organizations needs. But i would like to make it be able to Highlight the Names in the Calendar based off the information from the Main "Project_Tracker" sheet.

So if the Order type is AT in "Project_Tracker" sheet the then it would find the Last name in the "Project_Tracker" sheet and highlight the name in the "Calendar" a certain color. And have it highlight different colors for different order types.

If this is something that is possible please help me figure it out so i can implement in different spreadsheets.

Thank you,
RC Gonzalez

That looks like one of my calendar files. It would be possible to do this, although it would be a bit awkward, as (potentially) each cell could be a different order type and so you would need to expand the table in blue on the Project tracker sheet to include the type for each day. That's easy enough - you could put this formula in cell O1:

=IF(OR(K1="",K1="-"),"",INDEX(D:D,K1)&"")

and copy it down. Incidentally, you need to copy those formulae in blue down further, as you are missing some of the data.

However, conditional formatting cannot refer to conditions on other sheets, and so you will need to bring across the relevant data onto the Calendar sheet, so that you could then refer to it in the CF conditions, and that is where the awkwardness comes about. You could position it in column M onwards, for example, but that then messes the sheet up a bit. You could also think about including the order type in what you display on the calendar, in which case you should change the formula in cell L1 of the Project_tracker sheet to this:

=IF(OR(K1="",K1="-"),"",INDEX(A:A,K1)&"-"&INDEX(D:D,K1)&"")

and copy this down (no need for column O in this case), and then you could pick the order type up from that cell in the calendar. You show 6 order types on Sheet1, so each cell would need 6 different CF conditions.

Hope this helps.

Pete

Yeah I believe it was yours. It's pretty much perfect for what I needed.
I will try this tomorrow at work. Thank you so much for the quick reply.

Originally Posted by Pete_UK

That looks like one of my calendar files. It would be possible to do this, although it would be a bit awkward, as (potentially) each cell could be a different order type and so you would need to expand the table in blue on the Project tracker sheet to include the type for each day. That's easy enough - you could put this formula in cell O1:

=IF(OR(K1="",K1="-"),"",INDEX(D:D,K1)&"")

and copy it down. Incidentally, you need to copy those formulae in blue down further, as you are missing some of the data.

However, conditional formatting cannot refer to conditions on other sheets, and so you will need to bring across the relevant data onto the Calendar sheet, so that you could then refer to it in the CF conditions, and that is where the awkwardness comes about. You could position it in column M onwards, for example, but that then messes the sheet up a bit. You could also think about including the order type in what you display on the calendar, in which case you should change the formula in cell L1 of the Project_tracker sheet to this:

=IF(OR(K1="",K1="-"),"",INDEX(A:A,K1)&"-"&INDEX(D:D,K1)&"")

and copy this down (no need for column O in this case), and then you could pick the order type up from that cell in the calendar. You show 6 order types on Sheet1, so each cell would need 6 different CF conditions.

Hope this helps.

Pete