darkgear.com
New Member
- Joined
- Mar 10, 2005
- Messages
- 10
This is my first attempt at making a departmental calendar to track vacation and also to view who is taking vacation on what day. I also want to be able to filter by individual departments.
I started with a simple entry form.
I then made a calendar and started working on the formula to display who was on vacation for the day and made it possible to filter by department.
K1 is data validated so you can select one of the departments.
Ugly formula I used to get the names to display...
{=IF($K$1="",MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,'Data Entry'!$B$2:$B$70,"")," "),MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,IF($K$1='Data Entry'!$C$2:$C$70,'Data Entry'!$B$2:$B$70,"")," ")))}
My question is is there a better way to handle the display of names? Using MCONCAT works but it places an extra space(or comma or whatever I use to separate names) after every row that it did NOT find a match. So towards the end of the year I could end up with scores of spaces before the first name shows up.
Best regards,
Randy
I started with a simple entry form.
2006-Calendar.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Date | EE | Dept | PTO | ||
2 | 1/1/06 | Albert | USS | 8 | ||
3 | 1/1/06 | Mercy | Rating | 8 | ||
4 | 1/3/06 | Naomi | Records | 8 | ||
5 | 1/4/06 | Dustin | Records | 8 | ||
6 | 1/5/06 | Albert | USS | 4 | ||
7 | 1/5/06 | Amy | Rating | 8 | ||
8 | 1/5/06 | Donna | Clerk | 8 | ||
9 | 1/5/06 | Dorothy | Clerk | 8 | ||
10 | 1/5/06 | Dustin | Records | 8 | ||
11 | 1/5/06 | Linda | Records | 8 | ||
12 | 1/5/06 | Miriam | Rating | 2 | ||
13 | 1/5/06 | Naomi | Records | 4 | ||
14 | 1/5/06 | Polly | Rating | 8 | ||
15 | 1/5/06 | Sophia | Records | 8 | ||
16 | 1/6/06 | Donna | Clerk | 8 | ||
17 | 1/6/06 | Nita | Rating | 8 | ||
Summaries |
I then made a calendar and started working on the formula to display who was on vacation for the day and made it possible to filter by department.
2006-Calendar.xls | ||||||
---|---|---|---|---|---|---|
I | J | K | L | |||
1 | Department | |||||
2 | Thursday | Friday | ||||
3 | 5 | 6 | ||||
4 | Albert Amy Donna Dorothy Dustin Linda Miriam Naomi Polly Sophia | Donna Nita | ||||
Jan |
K1 is data validated so you can select one of the departments.
Ugly formula I used to get the names to display...
{=IF($K$1="",MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,'Data Entry'!$B$2:$B$70,"")," "),MCONCAT(IF(VALUE(CONCATENATE("1","/",J3,"/","2006"))='Data Entry'!$A$2:$A$70,IF($K$1='Data Entry'!$C$2:$C$70,'Data Entry'!$B$2:$B$70,"")," ")))}
My question is is there a better way to handle the display of names? Using MCONCAT works but it places an extra space(or comma or whatever I use to separate names) after every row that it did NOT find a match. So towards the end of the year I could end up with scores of spaces before the first name shows up.
Best regards,
Randy