[solved] Conditional Formatting - what am I doing wrong?

[nukularpower]

Hello all If anyone with a good eye can take a quick look at this, I'd be much obliged. I'm trying to set up a sort of quickview attendance calendar that auto formats. I sort of got it working but the results are not coming up like I want. Since a picture is worth at least 100 words:

This is the calendar itself (obviously not formatted yet.) What I want is for each day to turn red if a person missed that day. The cells contain dates formatted to show as single digits.

http://imgur.com/Fxxnf8D

This is the data sheet that is being pulled from. As you can see from the calendar above, all that seems to matter based on my formula is what Bob's last date was - if I change the last entry for Bob in this list, all the other red cells move to that date.

http://imgur.com/96Vooni

Here is the formula I am using. I want to check if a person has an absent entry, and if so, is it on this date, right? DV_LIST is just attendance codes and should never change.

=AND(IF(VLOOKUP($C9,Documentation!$A$2:$D$1000,4)=DV_List!$K$4,TRUE,FALSE),IF(VLOOKUP($C9,Documentation!$A$2:$B$1000,2)=D9,TRUE,FALSE))

When I paste this formula into the regular cells and spread it around, the references seem to adjust correctly, including the D9 (which is the current date cell.) Can anyone give me a pointer here? :D

[FDibbins]

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

And, in case those are not pictures...Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

[FDibbins]

1st, you dont need the TRUE/FALSE part, AND will return 1 of those and that is all CF needs, so just...
=AND(VLOOKUP($C9,Documentation!$A$2:$D$1000,4)=DV_List!$K$4,VLOOKUP($C9,Documentation!$A$2:$B$1000,2)=D9))

2nd, cant see what you are referencing, but does the C9 reference adjust across? If so, remove the $

[nukularpower]

Double post

[nukularpower]

Figured out how to attach the book As I said in the OP, what I'm trying to accomplish is getting the specific dates listed on the Documentation sheet to turn red on the calendar.

Also no, the $C9 is the column for people names, want it to only adjust down

[FDibbins]

From what I can make out, it seems to be working ok.

You can refine the CF formula to just this...
=AND(VLOOKUP($C9,Documentation!$A$2:$D$1000,4)=DV_List!$K$4,VLOOKUP($C9,Documentation!$A$2:$B$1000,2)=D12)

In CF, the formula always shows the reference as being to the top left cell, not to any others (wierd, I know, but thats how it works)

What is not happening that you expected to?

[nukularpower]

Hmm, will give that one a try. What's happening right now is that it's only coloring columns based on the last entry for "bob". It should be coloring each specific date cell for each person as listed in the documentation sheet, not columns - if you change Bob's last entry on the documentation sheet, all the colored cells change to that same date, rather than being scattered around like they should be. Bob should have a red 1 and 10 on the calendar, as well, instead of just 10.

edit: same result with that one you posted

[FDibbins]

vlookup will only search for the 1st "find", it will then stop looking.

I think you need to add a helper column to combine the name and date on Doc sheet. I added a new column at A, you can hide this if needed...
A2=B2&C2
copied down

Then change teh CF rule to this...
=AND(VLOOKUP($C9&D9,Documentation!$A$2:$E$1000,5,0)=DV_List!$K$4,VLOOKUP($C9&D9,Documentation!$A$2:$C$1000,3,0)=D9)

[nukularpower]

Perfect, thanks so much! That's excatly what I was looking for. Now if only VLOOKUP could work from the right instead of left so I didnt have to adjust all my other macros and stuff...

[FDibbins]

vlookup wont (normally) work right to left, but INDEX/MATCH will.

I creaed a new helper in I (which will become H if you delete the A helper column), and use the same formula to combine the name/date.

Then I used this for the CF...
=AND(INDEX(Documentation!$E:$E,MATCH($C9&D9,Documentation!$I:$I,0))=DV_List!$K$4,VLOOKUP($C9&D9,Documentation!$A$2:$C$1000,3,0)=D9)

You will note I only changed 1 of teh VLOOKUP's, I leftthe otrher 1 for you - let me know how you make out?