+ Reply to Thread
Results 1 to 5 of 5

Passing Values When Conditionally Formatted

  1. #1
    Registered User
    Join Date
    01-12-2018
    Location
    On A Lake, VA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Passing Values When Conditionally Formatted

    I have attached a Gantt Chart styled spreadsheet I am currently using to project the "Level of Effort throughout an entire day. Right now I simply type in the Event Start & End times and give it a Description. The conditional formatting handles the creation of the fill areas. I now also want to add a Level Of Effort scale (1 - 5) to each event. With a subtotal of the LoA at the bottom of the table for each half hour span of time. Right now I enter the LoA into column D. Then I also have to add it to the conditionally formatted cells and drag across. Is there a way to carry over the LoA # from column D into only the conditionally formatted cells. I would like to just add LoA into Column D, and it would auto populate where the conditional format has filled.
    Attached Files Attached Files

  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,604

    Re: Passing Values When Conditionally Formatted

    Do you want to show the individual LOAs for each half-hour, in addition to the CF highlights?

    I would suggest that you use a formula to populate each half hour cell with the LOA if that half-hour falls within the scope of the start and end times, and to modify your CF condition so that it highlights if the cell is not blank. You can use this formula to populate the LOA in F3:

    =IF(OR(Table52[[#This Row],[Start]]="",Table52[[#This Row],[End]]=""),"",IF(AND(F$1>=$A3,F$1<$B3),$D3,""))

    This should copy down automatically, but you also need to copy it across into G3:BB3 in order to populate all the columns (remove your manually-added numbers first). I then modified the CF condition to:

    =F3<>""

    and on the formatting you can set the foreground colour to be the same as the background if you don't want to see the individual numbers. I also changed the range in the Applies To box to suit the range used here, i.e. $F$3:$BB$17.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Passing Values When Conditionally Formatted

    You need a formula in every cell that uses the same logic you use in your CF formula. See attached.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    01-12-2018
    Location
    On A Lake, VA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Passing Values When Conditionally Formatted

    Thank you both. I could accomplish the reverse of what I wanted to do . . . so I guess all I needed to do was turn it around and attack the issue from the opposite angle. Both solutions work great

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

    Re: Passing Values When Conditionally Formatted

    Glad to help - thanks for the rep.

    Pete

+ 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. Replies: 1
    Last Post: 01-04-2017, 06:00 PM
  2. Replies: 1
    Last Post: 01-04-2017, 05:59 PM
  3. Calculating from conditionally formatted values
    By OneLooseCrank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2016, 05:12 PM
  4. Cell A1 should always be conditionally formatted
    By jostre in forum Excel General
    Replies: 1
    Last Post: 01-13-2016, 01:32 PM
  5. [SOLVED] How to Sum cells that have been conditionally formatted
    By kbaz555 in forum Excel General
    Replies: 2
    Last Post: 08-25-2014, 12:24 PM
  6. Sum of Items Conditionally Formatted
    By elfvis in forum Excel General
    Replies: 4
    Last Post: 12-13-2011, 09:32 PM
  7. Sum conditionally formatted cells
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2010, 02:22 PM

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