Vlookup not returning values

[vamshi57]

I did a vlookup in worksheet " Pivot Sheet " in columns Q & R , which apparently are not working , I'm not sure why .

1. I want to apply conditional formatting to row's O & P based on Q & R columns . If the actual login is later than the sced login time , Cell should turn RED , similarily if the Actual logout time is earlier than the sced logout time it should turn red .
2. Once conditional formatting is done , now is there a way I can see how many times ( count ) that person logged in late & logged off late ..

Note - Assumption - logins & logouts happen round the clock .

[JBeaucaire]

Try changing your N5 formula to:

=B5+0

...and copy down. Your column B values are text strings, while the column A values on "Actual Logins" are numeric. This will make them read the same.

Q5: =VLOOKUP($N5,'Actual Logins'!$A:$D,3,0)
R5: =VLOOKUP($N5,'Actual Logins'!$A:$D,4,0)

[vamshi57]

I tried adding "0 " but my login times doesn't seem to be returning . Most of the cells are returning "0's " while logout times seems to be returning fine .

Also when I do conditional formatting .. I don't know why its turning the cells as I wanted to .

[JBeaucaire]

1) I'm really no fan of VLOOKUP(). INDEX/MATCH is the most robust of all lookup tools, it only takes a little more time to get used to reading, and once you do, it works for anything. It replaces LOOKUP, VLOOKUP and HLOOKUP and suffers from none of the limitations they have.

Q5: =IF(ISNUMBER(MATCH($N5, 'Actual Logins'!$A:$A, 0)), INDEX('Actual Logins'!C:C, MATCH($N5, 'Actual Logins'!$A:$A, 0)), "")

Copy that down the Q column and across to the R column and you're good to go on that.


=============
I looked at the macros in your sheet, the macro in Module 2 doesn't belong there and won't work from there.

To use a Worksheet_Change macro, you have to put that in the sheet module where you want that to be active. So move that into the sheet itself, you'll find them listed in the VBAProject above the modules.

I typically give global permission to Excel to make changes with macros even though I've protected the sheets from USERs making changes. The easiest way to do that is with a workbook_open macro stored in the ThisWorkbook module or a Worksheet_Activate macro in the sheet module.

In ThisWorkbook:

Please Login or Register  to view this content.

In the Sheet module:

Please Login or Register  to view this content.

If you wanted to put the permissions part in the sheet, too, instead of Thisworkbook, then this version will work in the sheet module:

Please Login or Register  to view this content.

[vamshi57]

It worked for me & I liked the Index/Match too . However my conditional formatting is still not working .. Its still showing all RED's .

[JBeaucaire]

Your column O:P values are not the same kind of values as your column Q:R values. O:P have dates in them as well, meaning even though you've formatted them both to appear the same, they aren't.

Two ways to fix it...you can adjust your column O:P formulas to strip off the date portion, like so in O5:
=MOD(D5,1)+12.5/24

...or you can leave the formulas as is and let the conditional formatting do the same thing. Highlight O5:P531 and use this conditional formatting formula:
Condition1: Formula Is: =Q5<MOD(O5,1)

========
I'd love an explanation of what you're doing to the values from column D into column O. It makes no sense to me at all.

[vamshi57]

Now it worked for me .. In reference to your question "12.5/24 " .. Column D have times in MST ( Mountain standard time ) , so in column O I'm converting them to IST ( Indian standard time ) . Thanks a lot for your time in fixing this .