+ Reply to Thread
Results 1 to 7 of 7

Formula to fill cells based on date and drop down status

  1. #1
    Registered User
    Join Date
    09-01-2016
    Location
    Bangkok
    MS-Off Ver
    office 365
    Posts
    6

    Lightbulb Formula to fill cells based on date and drop down status

    I'm trying to edit someone else's gnatt chart in Gsheets to change the colours of the timelines based on the status.

    The sheet I'm editing changes the colour based on the status of a check box. However, I need a formula that will pull in the status based on a drop down: 'in progress', 'delayed' and 'complete', then I can use conditional formatting to colour the cells for each status.

    image.png

    In this example, the original format is top row, want I want is below, with the drop down menus

    Please Login or Register  to view this content.
    That is the current formula.

    What formula do I need, so it will pull in the text of the status?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    Re: Formula to fill cells based on date and drop down status

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    09-01-2016
    Location
    Bangkok
    MS-Off Ver
    office 365
    Posts
    6

    Re: Formula to fill cells based on date and drop down status

    Please find sample file attached.

    Row 4 is the original, rows 5, 6, 7 and 8 is what I would like to happen when adding the dates and selecting the current status.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    Re: Formula to fill cells based on date and drop down status

    You may donot need formula in range from I4, just Conditional for that range. Delete them.

    Conditional Formatting formula in I5:
    Red:
    =AND(I$2>=$D5,I$2<=$E5,$H5="DELAYED")

    Similar to "In progress" with H5="IN PROGRESS", "complete",...

  5. #5
    Registered User
    Join Date
    09-01-2016
    Location
    Bangkok
    MS-Off Ver
    office 365
    Posts
    6

    Re: Formula to fill cells based on date and drop down status

    Thanks for the reply.

    Not sure I follow though. Are you saying I should set a custom conditional formatting, using that formula to format the cell? =AND(I$2>=$D5,I$2<=$E5,$H5="DELAYED")

    I tried that, but it doesn't seem to format the cell:

    Attachment 714163

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,645

    Re: Formula to fill cells based on date and drop down status

    I attached a sample for "DEPLAYED"
    Insert more rules for the last colour
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-01-2016
    Location
    Bangkok
    MS-Off Ver
    office 365
    Posts
    6

    Re: Formula to fill cells based on date and drop down status

    Great, this looks like what I need.

    Thank you for your help.

+ 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. Formula to fill cells based on a drop down list
    By ljenkins73 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-08-2020, 05:52 PM
  2. Formula for status update based on date
    By steven.lasseigne in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-22-2018, 03:47 AM
  3. Determine status based on today's date (how to write the IF formula?)
    By butters7388 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2018, 09:45 AM
  4. Auto-fill Cells based on Drop Down Menu Choice
    By forestavekids in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-15-2016, 06:07 PM
  5. [SOLVED] Fill Cells Based on Day of Week Drop Down on VBA Form
    By drsadistic in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-06-2015, 11:33 PM
  6. Formula to fill cells based on the date in another cell
    By PowerPlayer23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2012, 02:26 PM
  7. Fill cells based on selection from a drop down on left
    By sa02000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2011, 04:58 AM

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