Work Spread sheet.

[dvincer23]

Im trying to design a spreadsheet for work that calculates pre approved absences from my workers. So far i hvae design the spreadsheet but im running into problems. Here is a small example of what i have:
(the dots are to keep my table in shape)
......A .......|....B......|...C...|.....D | E
1|Names....|............|John..|.Carm.| Jill
2|Absences|VAC used| 0.....| 0.....| 0
3\.............|SL used | 0.....| 0....| 0
4|.............|PL used | 0.....| 0.....| 0
5|.............|LOA used| 0.....| 0.....| 0
6| JULY......|.......1....|.......|.......|
7|.............|.......2....|.......|.......|
8|.............|.......3....|.......|.......|
9|.............|.......4....|.......|.......|

ok so whats going on here is that in the months boxes i created drop down menus that contain the flowing data:
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: #ffc000; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" width=64 height=17>LOA</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: #009999; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>VAC 0,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: #009999; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>VAC 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 0,50</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 1,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 2,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 3,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 4,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 5,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 6,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 7,5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BACKGROUND: red; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; mso-pattern: black none" height=17>SL 8</TD></TR></TBODY></TABLE>

When i select any of these i want them to count in the cells C2 to C5 (for the first user) But the problem i am having is that i can get it to count (for example) VAC 1 because its whole number. But when i select VAC 0.5 i want it to add as a .5 value.

example:

John has taken a VAC 1 on July 1st and VAC 0.5 on July 2nd and i want to total beside "VAC used" to be 1.5


......A .......|....B......|...C......|.....D | E
1|Names....|............|John....|.Carm.| Jill
2|Absences|VAC used| 1.5....| 0.....| 0
3\.............|SL used | 0.......| 0....| 0
4|.............|PL used | 0.......| 0.....| 0
5|.............|LOA used| 0.......| 0.....| 0
6| JULY......|.......1....|VAC1..|.......|
7|.............|.......2....|VAC0.5|.......|
8|.............|.......3....|.......|.......|
9|.............|.......4....|.......|.......|

is there any way to do this? Help is greatly appreciated!

 

[shemayisroel]

(the dots are to keep my table in shape)

Try using excel jeanie to post your sample data:

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

 

[dvincer23]

do you want me to do it now? or is this ok for this time?

 

[shemayisroel]

do you want me to do it now? or is this ok for this time?

That would be great as it would be much easier to work with...

 

[XxCALLofBOOTYxX]

LOL, "post #1" must have taken forever to do.... Good job though.

 

[dvincer23]

Excel Workbook
	A	B	C	D	E	F
1	NAME		Baillargeon Robert	Beaulieu Michel	Boisvert Marc	Boivenue Michel
2	ABSENCE	ID #	1045	1087	1210	1205
3		VAC earned	28
4		VAC used	0	0	0	0
5		SL used	0	0	0	0
6		PL used	0	0	0	1
7		LOA used	0	0	0	0
8	JULY	1	SL 3
9		2
10		3
11		4		VAC 1
12		5
13		6				PL
14		7
15		8
16		9
17		10
18		11
19		12
20		13
21		14
22		15
23		16
24		17
25		18
26		19
27		20
28		21
29		22
30		23
31		24
32		25
33		26
34		27
35		28
36		29
37		30
38		31
Absences

 

[shemayisroel]

When i select any of these i want them to count in the cells C2 to C5 (for the first user) But the problem i am having is that i can get it to count (for example) VAC 1 because its whole number. But when i select VAC 0.5 i want it to add as a .5 value.

When running your count if formula for anything thats .5 could you not divide the result by 2?

 

[dvincer23]

When running your count if formula for anything thats .5 could you not divide the result by 2?

The formulas i have there count the basics. i dont know were to go to count 1 & 0.5. the problem i run into is that i hvae to turn the text into the number i want e.g VAC 1 = 1 and VAC 0.5 = 0.5 . I have no idea what to do. I looked and looked and im running out of ideas.

 

[shemayisroel]

The formulas i have there count the basics. i dont know were to go to count 1 & 0.5. the problem i run into is that i hvae to turn the text into the number i want e.g VAC 1 = 1 and VAC 0.5 = 0.5 . I have no idea what to do. I looked and looked and im running out of ideas.

Not sure why your doing that, I'd recommend you go through the below helpful examples on COUNT and see if you can change your approach

http://www.contextures.com/xlFunctions04.html

http://office.microsoft.com/en-us/excel/HP052090291033.aspx

 

[dvincer23]

is there not a way were i can turn the text into Values? such as VAC1 into 1 and VAC 0.5 into 0.5... and so on?