Conditional Formatting Across Multiple Spreadsheets

[DSwartz]

I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.

I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.

There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.

I hope that makes sense. Thanks in advance for any assistance that can be provided!

[Gregor y]

two thoughts: since you appear to have a cell formula that gets you close to what you want you could use that formula either

a. to set a value in an adjacent column, use that column to format your target cells, then hide the extra formula columns.

b. use the formula in a defined name, then use the defined name within the conditional format.

however without an example spreadsheet it's hard to really test these two out to see which works best.

[DSwartz]

Test.xlsx

Gregory, thanks for your help. I've attached a sample workbook with fake data plugged in...hopefully this gives everyone a better idea of what I'm looking to do here! I'm still unsure about this one so any additional help would be greatly appreciated. Thanks again!

[Gregor y]

Option b. wasn't really working for me right off the bat, so here's option a.

Steps:

1. on tabs 06-14 and 06-21 insert blank columns F:H and hide them
2. on tab Training insert blank column B
3. on tab Training cell B1

   Please Login or Register  to view this content.

4. on tab Training cells B2:B8

   Please Login or Register  to view this content.

5. on tab Training cells C2:E8 conditional format formula

   Please Login or Register  to view this content.

6. on tab Training hide column B

Explaination:

1. this squares up the data so you don't have a gap missing in the formulas for Sunday
2. this is the helper formula column
3. this is the reference to the first entry in this weeks schedule
   1. example: Today 6/17/14 = '06-14'!E5
   2. example: Today 6/25/14 = '06-21'!E5
4. this is the offset for this employee for today
   1. example: Emp3 on Fri = Row(5+2), Col(5+6*3)
   2. example: Emp1 on Sat = Row(5+0), Col(5+0*3)
5. this is the format conditional formula make sure you select the range starting in cell C2
6. optional step you can hide the scheduled column if you don't want to see it

[DSwartz]

This worked perfectly! Thanks for your help!