Conditional Formatting Problem - Dynamic Formula??
Setting up a mock-up for your first table, I've used two header rows
so what you refer to as Row 1 is actually row 3, and the 14 is in cell
B1 with the first date in B2. Select cell B3 and click on Format |
Conditional Formatting and choose Formula Is rather than Cell Value Is
in the first box, then put this formula in the next box:
=AND(TODAY()<B$2+21,TODAY()=B$2,B3<"",COUNTIF(E3 :$W3,"<")=0)
Click on the Format button, then on the Patterns tab (for background
colour) and choose your colour. Click OK twice to exit the dialogue
boxes.
Then use the Format Painter icon to copy that format down and across
your data as required.
Hope this helps.
Pete
On May 1, 7:36*pm, GoBucks wrote:
I am trying to figure out a complex CF formula that will format a section of
a row based on mutltiple conditions. Here is a sample table of type of data I
am working with:
Week# * 14 * * *15 * * *16 * * *17 * * * 18 * *19 * ... *35
Date * *4/3 * * 4/10 * *4/17 * *4/24 * *5/1 * 5/8 * * *12/31
Row1 * *10 * * *10 * * *10 * * *
Row2 * *100 * * 100 * * 100 * * 100
Row3 * *50 * * *50 * * * * * * *
In each row, I would like to cond. format the cells that CONTAIN values from
Week#'s 14 -16 ONLY IF there are NO values present from Week #17 thru Week
#35. This is assuming that the current week = Week 14. For example:
Row1: *CF * CF * *CF
Row2: *no CF (values present after WK 16)
Row3: *CF * CF
When in Week 15, I would like the CF range to be from WK 15 to WK 17 with NO
values from WK 18 on. I have a cell on the sheet that used for the current
date input that I was trying to use as a cell reference for a formula. I've
been struggling with this for a few days. Any help is greatly appreciated..
|