2010 Holiday Form - Formula Help

[2010]

Hello,

I am currently creating an annual leave formula for the company I work in and I need help.

I have created 4 headings

<TABLE style="WIDTH: 195pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=260><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; WIDTH: 51pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 height=20 width=68>Holiday</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 width=64>Lieu</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 width=64>Sick</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 width=64>Other</TD></TR></TBODY></TABLE>

And I need to create a drop down list containing; Holiday: All Day, Holiday: Half Day AM, Holiday: Half Day PM, Lieu Day: All Day, Lieu Day: Half Day AM, Lieu Day: Half Day PM, Sick, Other: All Day, Other: Half Day AM, Other: Half Day PM.

I know how to create the drop down list but I need to create a value for the Days, ex. Holiday: Half Day AM. would equal 0.5 when selected from the list and show as 0.5 under the Holiday column.

And when I select Other: All Day, it would equal 1.0 under the Other Column.

Can someone please help me on resolving this issue as I have created my spreadsheet but am unable to get past this stage.

I appreciate any forthcoming help!!!!!!!!!

Thanks.

 

[Robert Mika]

This is one of many approaches to your problem.

http://www.excelaris.net/Download/Calendar.xls

Your look up table can be localize anywhere on spreadsheet or even on different spreadsheet.

Hope that helps

 

[scottylad2]

Excel Workbook
	I
18	holiday
19	1
Days per month

adjust the criteria to suit your own wording

 

[scottylad2]

Excel Workbook
	I
18	half day am
19	0.5
Days per month

I have a dropdown with my keywords, and the value 1 or 0.5 will display according to whats showing in the dropdown

 

[scottylad2]

Excel Workbook
	I
28	1
Days per month

This works as well, formula in I28 and dragged along. The cell references are to where my list is

 

[scottylad2]

Excel Workbook
	I	J	K	L
26	holiday	sick	lieu	other
27	All Day	sick am		All Day
28	1	0.5		1
Days per month

ooops forgot to copy my table lol

 

[2010]

Thanks for all the help, it's appreciated!!