I have this anoying alert msg "You cannot use references to other worksheets or workbooks for CF criteria"
Is there an easy way to apply conditional formatting in one worksheets based on cell values in other worksheet but in the same workbook?
Here is what I have.
I have main tab with some names in column B and C and their job title in column D. (Range B3:D29)
Now, I have a calendar for 2011 spread out for each name in a single row so I have 27 rows x 365 columns (Range E3:NE29)
Every cell is formated as a date (1-Jan etc.) cos I was thinking I would be able to make relationship with another tabs which I have in my workbook. Those tabs contain detailed information about one cingle name from my main tab with listed names and they are
all linked. So I have 27 tabs. Each tab containes also a calendar but in a normal view, months separated, with 7 days in a row and dates in matching cells below. That calendar contains Conditional formatting based on some cells I have on a left side of that
calendar.
Now, I wanted to have that formatting applied to my main tab with all names listed. I tried to direct formula in conditional formatting window to those cells on which is based my CF so it would do the same thing in my main worksheet but I get this message that
it is not possible.
Then because I didnt now how to solve this I copied those cells into my main tab with list of all names and I pointed my formula into that direction but in this case I would have so many formulas in CF and for every single name I have on the list and plus I
would have all those cells on my main tab, even though I can hide them but I would have 27 times 16 cells I have in every tab so I would have extra 432 cells. One of the options I was thinking is to have combo list with all 27 names and when name is changed
to populate those 16 cells with appropriate info and then I would have one CF formula. But then how to link that formula to each name... It is a bit complicated but I know I am not making space shuttle. It could be solved but not by me I guess. Below is sample
of main tab and tab with details.
Main tab
| 4 | John | Doe | Logistics | 1-Jan | 2-Jan | 3-Jan | 4-Jan | 5-Jan |
etc
Tab with details
Cells that CF is based on
| 30-Mar-2011 |
| 31-Mar-2011 |
| 20-Apr-2011 |
| 21-Apr-2011 |
Cells with calendar
| April | ||||||
| Mo | Tu | We | Th | Fr | Sa | Su |
| 1-Apr | 2-Apr | 3-Apr | ||||
| 4-Apr | 5-Apr | 6-Apr | 7-Apr | 8-Apr | 9-Apr | 10-Apr |
| 11-Apr | 12-Apr | 13-Apr | 14-Apr | 15-Apr | 16-Apr | 17-Apr |
| 18-Apr | 19-Apr | 20-Apr | 21-Apr | 22-Apr | 23-Apr | 24-Apr |
| 25-Apr | 26-Apr | 27-Apr | 28-Apr | 29-Apr | 30-Apr | |
