+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting Similiar Blocks with relative references

  1. #1
    Registered User
    Join Date
    11-27-2023
    Location
    CA
    MS-Off Ver
    MS Excel OSX V16.33
    Posts
    1

    Angry Conditional Formatting Similiar Blocks with relative references

    I have a scheduling document that I'd like to turn an event block gray if no data is entered in 3 specific blocks. I'd then like to copy that conditional formatting to the other event blocks so it properly references the appropriate specific blocks in that event. When i do this, it references the absolute referenced cells from where it was copied, and if i remove the absolute, then only the rows and columns in the specific blocks gets formatted.

    Example simplified:
    [A1] [B1] [C1] [D1] [D1] [E1]
    [A2] [B2] [C2] [D2] [D2] [E2]
    [A3] [B3] [C3] [D3] [D3] [E3]
    [A4] [B4] [C4] [D4] [D4] [E4]

    [A5] [B5] [C5] [D5] [D5] [E5]
    [A6] [B6] [C6] [D6] [D6] [E6]
    [A7] [B7] [C7] [D7] [D7] [E7]
    [A8] [B8] [C8] [D8] [D8] [E8]

    For A1:E4, if "$B$2,$D$1,$E$1 are all blank", color A1:E4 gray. Works good for A1:E4. Copy conditional formatting and merge to A5:E8. The formula for conditional formatting remains $B$2,$D$1,$E$1 and i need to manually change to be $B$6,$D$5,$E$5.

    If I do, "B2,D1,E1 are all blank", then only column B,D,E and rows 1 and 2 in that range get colored gray.

    It's almost like I need absolute references within each event block A1:E4, that are then changed relative to the new event block when pasted.

    Attached excel sheet for more help, Example above does not match cell numbers in document as it's a bit larger.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,593

    Re: Conditional Formatting Similiar Blocks with relative references

    Perhaps something like this:
    Populate cells BI8:BI97 using: =IF(B8="Brf",AND(ISBLANK(C9),ISBLANK(E8),ISBLANK(I8)),BI7)
    Use the following as the conditional formatting rule for A8:J97 =$BI8
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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 Not Updating Relative References
    By mblessing89 in forum Excel General
    Replies: 3
    Last Post: 09-18-2020, 08:53 AM
  2. Conditional Formatting and relative references
    By kath85 in forum Excel General
    Replies: 1
    Last Post: 06-29-2020, 05:31 PM
  3. Conditional formatting - Relative references (2010)
    By banks334 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2015, 03:29 PM
  4. Conditional formatting - Relative references (2010)
    By banks334 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-06-2015, 01:44 PM
  5. Replies: 3
    Last Post: 10-08-2013, 09:34 AM
  6. Replies: 4
    Last Post: 08-25-2012, 11:41 AM
  7. Replies: 5
    Last Post: 09-19-2010, 05:33 AM

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