Desperate for Formula Help

[asad]

Hello,

I got the following:

Excel Workbook
	A	B	C	D	E	F	G
4			Shift			Name
5	A/L	RDO
6			AM GROUP RED
7	0		1		DF034	MATAU
8	A/L		2		DF042	REEVE	#REF!
9	A/L		3		DF044	ROZE	A/L 15/05/11
10	0		4		DF066	SAMBEVSKI
11	0		5		DF046	SANDOVAL
12	0		6		DF048	SEBASTIAN
13	0		7		DF086	SHEN
14	0		8		DF051	SIMBORO
15	0		9		DF112	SIPILIANO
16	0		10		DF055	SUNTOVSKI
17	0		11		DF056	TAPIA
18	0		12		DF089	TUSITALA
19	0		13		DF083	VITALE
20	0		14		DF059	VUU
Weekly Roster

I have not been able to get this to give me the desired result which should be

A/L 15/05/2011

.

Can somebody please tell me what's wrong with my formula?

Asad

 

[Matty]

Hi,

Rather than posting a formula that isn't returning the correct result and expecting us to try to decipher what's going on with it, much better to clearly explain what you want the formula to deliver given the sample data provided.

Could you do this so that we can try to help?

Matty

 

[asad]

Thanks for responding so quick.

I am trying to work out the last date for an employee to be on the annual leave.
Column A in my file got a formula that checks whether that particular employee is on leave for the current week that the roster is for or not. If yes, it puts A/L so show annual leave.

The cell with employee name in column F will become yellow if that person is on leave for this week.

Column G at the moment is filled manually. I want it to look in the same file as column A is looking. That is the file that contains information regarding employees being on annual leave or not and just to make it clear, I will post a screen shot of the portion from that file.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	2000>	800>									Name		2010
3	1750>	600>										28-Dec	4-Jan	11-Jan	18-Jan	25-Jan
4	1500>	500>										3-Jan	10-Jan	17-Jan	24-Jan	31-Jan
5											1	2	3	4	5	6
6	1250>	400>			862	734	4				Both Depots Total	45	46	44	42	17
7	1000>	300>									Sandringham Total	4	5	5	4	2
8											Balance + or - on Leave	#REF!	#REF!	#REF!	#REF!	#REF!
9	Sep-10				770	670	2				Footscray on LeaveTotal	41	41	39	38	15
10					397	241	2				AM Drivers on Leave	22	22	20	20	8
11					98	250	0				Relief Drivers on Leave	1	1	1	0	0
12											Balance + or - on Leave	#REF!	#REF!	#REF!	#REF!
13					275	179	0				PM Drivers on Leave	18	18	18	18	7
14					92	64	2				Sandringham Total	4	5	5	4	2
15	172:10	376:57	549:07	435:07	5	3	0	1	DF002	ADDICOTT, Steven Charles - Code: DF002 - Family Name: ADDICOTT	ADDICOTT
16	218:38	0:00	218:38	142:38	4	3	0	2	DF078	AGGETT, Brian Charles - Code: DF078 - Family Name: AGGETT	AGGETT
17	112:26	0:00	112:26	112:26	4	4	0	3	DF125	ANTONOVICH, Glen Anthony - Code: DF125 - Family Name: ANTONOVICH	ANTONOVICH				1
18	573:40	149:15	722:55	266:55	5	16	2	4	DF021	ARDON, Moises	ARDON	1	1	1	1
19	151:42	224:15	375:57	337:57	12	2	0	5	DF004	BACH, Hung Tan - Code: DF004 - Family Name: BACH	BACH. R
Leave Approved

In this file, 1 in a cell in any employee's row means he/she is on leave for that week. The dates in rows 3 and 4 are week starting and week ending dates. My formula looks at the week ending date.

I hope I haven't confused you guys. Please let me know if oyu need any more information. Thanks for your help.

Asad

 

[asad]

One more thing I wanted to ask you. When I put path of file in the formula, do I have to put in full path as shown in my first post even if both files are in same folder or just the folder name and the file name would fix it?

 

[asad]

.

 

[asad]

I think, if I could use the following formula, it would work.

index('[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4,1,min(if(len(INDEX('[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$201,MATCH($F10,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$K$201,0),MATCH($BC$1,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4,0)):INDEX('[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$201,MATCH($F10,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$K$201,0),MATCH(LOOKUP(TODAY()+1000,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4),'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4,0))=0,column(INDEX('[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$201,MATCH($F10,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$K$201,0),MATCH($BC$1,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4,0)):INDEX('[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$201,MATCH($F10,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$K$201,0),MATCH(LOOKUP(TODAY()+1000,'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4),'[Leave allocation 2011 MasterTrial.xls]Leave Approved'!$K$4:$FP$4,0))))

But when I try to put it in, I get the message the formula is too long and deletes everything.

I don't know what to do?

 

[Matty]

I am still struggling to understand I'm afraid.

In your original post, you reference the name 'REEVE', but I can't see this name in the second table you posted.

Moreover, could you explain what criteria needs to met on the 'Leave Approved' Worksheet in order for 'A/L 15/05/11' to be the result in the 'Weekly Roster' Worksheet?

Matty

 

[asad]

I am still struggling to understand I'm afraid.

In your original post, you reference the name 'REEVE', but I can't see this name in the second table you posted.

Moreover, could you explain what criteria needs to met on the 'Leave Approved' Worksheet in order for 'A/L 15/05/11' to be the result in the 'Weekly Roster' Worksheet?

Matty

The reason you can't see REEVE in the second table is that the woksheet is a very big one. I don't want to post the whole sheet here as it will take up lots and lots of room. But the name is definitely there in the top middle section of that list of names.
The criteria is simple. If REEVE was on leave from 2nd May to 15th May, I want the formula to find the last date of his leave and show that date in the cell as "A/L 15/05/2011" and if he was to be on leave untill 5th June, the cell will display "A/L 05/06/2011". The last date of leave will always be a Sunday as the annual leave are approved for whole weeks starting Mondays and ending Sundays.
I posted the second table just to show how we enter an employee's annual leave for any particular week.If the column for any week shows 1, then that means that the employee in that row is on leave for that week.

Please let me know if you want me to post a shot of the portion where "REEVE" name is written.

Thanks for your help. Asad

 

[Matty]

Is it just the last leave you're interested in?

If so, and using the data you've posted for testing purposes, try:

Weekly Roster

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:34px;"><col style="width:67px;"><col style="width:64px;"><col style="width:48px;"><col style="width:64px;"><col style="width:97px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td><td rowspan="2" style="font-family:Verdana; font-size:8pt; text-align:center; ">Shift</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td><td rowspan="2" style="font-family:Verdana; font-size:8pt; text-align:center; ">Name</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">A/L</td><td style="font-family:Verdana; font-size:8pt; ">RDO</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-style:italic; font-family:Verdana; font-size:8pt; text-align:left; ">AM GROUP RED</td><td style="font-style:italic; font-family:Verdana; font-size:8pt; "> </td><td style="font-style:italic; font-family:Verdana; font-size:8pt; "> </td><td style="font-style:italic; font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">A/L</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">1</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF021</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">ARDON</td><td style="font-family:Verdana; font-size:8pt; ">A/L 24/01/2011</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">2</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF042</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">REEVE</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">3</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF044</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">ROZE</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">4</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF066</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SAMBEVSKI</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">5</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF046</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SANDOVAL</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">6</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF048</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SEBASTIAN</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">7</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF086</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SHEN</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">8</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF051</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SIMBORO</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">9</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF112</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SIPILIANO</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">10</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF055</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">SUNTOVSKI</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">11</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF056</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">TAPIA</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">12</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF089</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">TUSITALA</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">13</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF083</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">VITALE</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">0</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; text-align:right; ">14</td><td style="font-family:Verdana; font-size:8pt; "> </td><td style="font-family:Verdana; font-size:8pt; ">DF059</td><td style="font-family:Verdana; font-size:8pt; text-align:left; ">VUU</td><td style="font-family:Verdana; font-size:8pt; "> </td></tr></tbody></table>
The formula in G7 is:

=IF(A7<>A$5,"",A7&" "&TEXT(MAX(IF('Leave Approved'!I$15:I$19=E7,IF('Leave Approved'!$L$15:$P$19=1,'Leave Approved'!$L$4:$P$4))),"dd/mm/yyyy"))

Which requires array entry, i.e. CTRL+SHIFT+ENTER.

If I'm off the mark here, please explain further.

Matty
http://www.excel-jeanie-html.de/index.php?f=1

 

[asad]

Thanks a lot Matty for your help.
I had to tweak this a bit, but finally it worked.
Thanks again.
Asad