Thread: Vlookup not returning values

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 .

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)

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:

Code:

Private Sub Workbook_open()
    Sheets("Sheet1").Protect "xxx", UserInterfaceOnly:=True
End Sub

In the Sheet module:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
        If Target.Locked = False Then Target.Locked = True
    End If
End Sub

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

Code:

Private Sub Worksheet_Activate()
    Me.Protect "xxx", UserInterfaceOnly:=True
End Sub

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.