+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting Project Calendar from Reference Table

  1. #1
    Registered User
    Join Date
    03-19-2019
    Location
    United States
    MS-Off Ver
    2016
    Posts
    2

    Conditional Formatting Project Calendar from Reference Table

    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?

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Conditional Formatting Project Calendar from Reference Table

    It would help if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-19-2019
    Location
    United States
    MS-Off Ver
    2016
    Posts
    2

    Re: Conditional Formatting Project Calendar from Reference Table

    This is actually a version of the workbook you posted here 6 years ago! I've attached it for your reference.

    I think I figured out my issue but I do have an additional question about this. As you can see, I've used Vlookup and a reference cell for each date. I then copied the format (copy, paste special - format) into the rest of the cells for each respective date. Why doesn't the CF formula change respectively? For example, the CF in P5 still references P4 although it does seem to be working and changing properly in the background. Is this expected in CF?

    Ideally, I would just create the rules in the first cell of the month and copy them over into each day. I'm nervous about doing this because the CF formula seems to work but doesn't physically change in the window.

    Thank you for your help!

    EDIT: For some reason the way we are referencing the dates_number wasn't working in CF. I found a workaround by creating Table2 which drops that first column and directly references my IDs. So instead of "A4&_&K15" I just used B3 or w/e in the CF formula. I hope this makes sense.
    Attached Files Attached Files
    Last edited by evred; 04-05-2019 at 02:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting Project
    By thedunna in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-19-2017, 12:40 PM
  2. Using a Named Table Reference in Conditional Formatting
    By cbrown6305 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2017, 04:10 AM
  3. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  4. How do I display project task lists (table) in excel calendar
    By Devota in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2016, 07:52 PM
  5. Replies: 3
    Last Post: 08-06-2014, 12:51 AM
  6. Conditional Formatting Formula's for Project Timeline
    By bunchomunkies in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2013, 11:41 AM
  7. Replies: 8
    Last Post: 06-28-2012, 01:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1