lookup formula

[sabi.kiss]

Hi there,

Hopefully someone could help me with this.
I'm trying to do a leave planner for each one of our sites.
In the top row I've got the full financial year, day-by-day. On another sheet I've got column A with employee names, column B with leave start date and column C leave finish date. I am looking for a formula, which in planner sheet, in the second row, puts a X under the actual date, if that date is between start and finish date on the Data sheet.
That way, instead of highlighting days for each employee, I could do a automated sheet.
Would this be possible?

Thanks,
Sabi

 

[Akashwani]

Hi Sabi,

Is this what you wanted?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	*	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr
2	Ak	X	X	X	X	X	X	X	X	*	*	*
3	Sabi	*	*	*	*	*	*	*	X	X	X	X
4	MrExcel	*	*	*	*	X	X	X	*	*	*	*
Planner

Excel Workbook
	A	B	C
1	Name	Start Date	End Date
2	Ak	14/04/2011	21/04/2011
3	Sabi	21/04/2011	25/04/2011
4	MrExcel	18/04/2011	20/04/2011
EmployeeDetails

The formula in B2 of Sheet Planner is this.....
=IF(AND(B$1>=EmployeeDetails!$B2,B$1<=EmployeeDetails!$C2),"X","")

Copy across and down as far as you need to go.

Good luck

 

[sabi.kiss]

Thanks a lot Akashwani..that work good.
My other question is, what about if you get a duplicate name? Would it record all the data? So, if you got my name twice, with different leave taken, in different months, would it record both?
Will give it a try when I get to work today.

Cheers,
Sabi

 

[sabi.kiss]

How could I integrate all this into a index/match formula, in case I've got duplicates, with different dates?

Thanks,
Sabi

 

[sabi.kiss]

I worked out how to integrate it in a index/match formula. The only issue is that when it's a duplicate, it just take the first value from the top.
Here's what I put together so far:

IF(AND(Planner!B$5>=(INDEX(Data!$B7:$B2000, MATCH(Planner!$A9, Data!$A7:$A2000,0))), Planner!B$5<=(INDEX(Data!$C7:$C2000, MATCH(Planner!$A9, Data!$A7:$A2000,0)))),"AL","")

Planner!B5 is leave planner starting date
Data!B column is employee leave starting date
Data!C column is employee leave finish date
Planner!A9 & Data!A7 is employee name field

Could anybody help me how to actually use duplicates.

Thanks,
Sabi

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	*	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr	19-Apr	20-Apr	21-Apr	22-Apr	23-Apr	24-Apr	25-Apr	26-Apr	26-Apr
2	Ak	X	X	*	*	*	X	X	*	*	*	*	*	*	*
3	Sabi	*	X	*	X	*	*	*	X	*	*	X	*	*	*
4	MrExcel	*	*	X	*	X	*	*	*	*	X	*	*	*	*
Planner

 

[njimack]

oops - realised thats not what youre after, i'll give it another go

 

[njimack]

Try the below UDF:

Excel Workbook
	A	B	C	D	E	F
1	*	14-Apr	15-Apr	16-Apr	17-Apr	18-Apr
2	Ak	X	X
3	Sabi		X	X	X
4	MrExcel			X	X	X
Planner

Function Date_Check(Nm As String, Dt As Date)

Dim Last_Row As Long
Dim i As Long
Dim s As String

Date_Check = ""

With Sheets("Data")
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To Last_Row
        If .Cells(i, 1) = Nm Then
            If .Cells(i, 2) <= Dt And .Cells(i, 3) >= Dt Then
                Date_Check = "X"
            Exit Function
            End If
        End If
    Next i
End With


End Function

 

[Akashwani]

Hi Neil,

Thanks again for resolving this for Sabi and myself.....
http://www.mrexcel.com/forum/showthread.php?t=545345

Thanks

Ak

 

[sabi.kiss]

that is absolutely fantastic. thanks a lot Neil.
is there a chance to add a command button, to actually refresh the Planner?
when i input new data, does not update the Planner sheet.

Sabi