• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Excel - problem with conditional formatting

I have a problem with some conditional formatting.  The attached spreadsheet should describe the problem.
I want to have a list of 5 workdays (B2 to B6).  Next to those are cells that can be selected with an "x".

Below in the spreadsheet is a list of dates (B10 to >>>>)

I simply want to bold up the dates in the list if their corresponding weekday in the top list is ticked (ie an "x" in the adjacent box).

I just cannot get it to work properly.  In the spreadsheet, Tuesday and Wednesday work fine, Thursday and Friday don't work and Monday bolds up Saturday, Sunday and Monday!!

It's driving me nuts and I'm sure it's just a simple mistake or lack of understanding.

The formulas in columns D and E are just debug (and there seems to be a fundamental problem with Friday!!)

Hope someone can throw some light on it.

Best regards

Richard
Conditional-formatting.xls
0
rltomalin
Asked:
rltomalin
  • 2
2 Solutions
 
RunriggerCommented:
try this formula;

=SUMPRODUCT(($B$2:$B$6=TEXT(B10,"Dddd"))*($C$2:$C$6="x"))
0
 
RunriggerCommented:
The erroneous results you are getting are a mystery and appears to be a bug!
0
 
krishnakrkcCommented:
Hi,

LOOKUP works only if your data is sorted. Another option would be

=INDEX($C$2:$C$6,MATCH(TEXT(B10,"dddd"),$B$2:$B$6,0))="x"

Kris
0
 
rltomalinAuthor Commented:
Hi Guys

Thanks for that.  Both solutions seem to work fine - so I will split the points.

Regards

Richard
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now