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.
Bookmarks