I have a workbook with two worksheets. The first is a calendar in which you can pick a month and year and it will automatically arrange the cells depending on the start day of the month. In each of these cells is an "area" column and a "project ID" column that are simply pulled from a much larger table on sheet 2. On sheet 2 is table (Table1) that updates daily with project IDs, due dates, areas, etc. As you change the date on sheet 1 in the calendar it updates with the data from sheet 2 to show you which projects are due on any given day/month/year. For example, if I chose April of 2019, the calendar might look like this.
Monday Tuesday 4/1/19 4/2/19 Area ID Area ID California X.12345 Wisconsin X.98765 Idaho X.22344 nevada X.88766
What I need is to create conditional formatting that shows whether a project is past due. I can do this in the table on sheet 2, no problem. The projects above all show up red because we are past those dates. (I used a nested If loop to compare today's date with the estimated completion date. This returns a "Past due" if the estimated date is less than today or a "Not yet due" if it is greater than today). What I can't figure out is how to make the CF show on the calendar itself, so that the project ID cells highlight accordingly. Essentially I want to carry my CF over from one sheet to another without having to add extra columns to my calendar.
I've tried using a CF like "=Vlookup($D3, Table1, 5, False) = "Past Due" but I get a formula error. $D3 is the cell of the project ID on 4/1/19 of my calendar (X.12345). There could be a $D4,5,6 etc for that day. Table1 is my data table on sheet 2 and 5 is the column number of "Past Due." I've tried changing "Past due" to numbers to use < or > operators but I get the same formula error. I can't wrap my head around how to link the value of a cell in one table to my calendar cell without physically adding that field to the calendar.... Do I need a macro?
If I enter =VLOOKUP($D3,Table1, 5, FALSE) into another random cell, it returns "Past due" as expected. Yet it doesn't like this in my CF statement.
Can anyone please help?
Bookmarks