I was advised to revise my question because maybe I wasn't clear enough...Here goes:
I have 4 separate Excel Workbook Files. Each file contains between 3 and 8 worksheets. The worksheets contain a yearly calendar showing weekends and holidays. The secretary or supervisor brings up an individual employees sheet, and they use a validation list dropdown box to select vacation days, personal day, etc. The calendar then can, at a glance, show the employees scheduled attendance for the entire year. Now, my boss has the secretary take the data off of these vacation record/attendance sheets and place them on a monthly calendar. This way, he can see the entire month and work coverage at one glance. This activity takes the secretary an inordinate amount of time, because of the constant changes being made with the individual employees vacations. So, what I am trying to do is have a sheet collect the names of all employees scheduled to be off any day of the year. I have it so that I put the dates of the month in separate columns. Under the columns, I have a formula in the cells underneath which goes out to the employee file and checks whether they have any 'V' (vacation) scheduled in the corresponding cell. If a 'V' does exist, then it puts the person name underneath the date column. I have approximately 30 cells underneath each date corresponding to each employee. For example:
November
1 2 3 4 5 6
Smith
Brown
Jones
Fred
Bob
The blanks represent any time the employee sheet shows that the employee referenced in that cell is scheduled to be in that day. Anyway, collecting this data is not my problem.
My problem is that I am using concatenate to take all of the values in each column and am trying to put them into a calendar template so that for any particular date, it will show a word wrapped list of anyone scheduled for vacation that day. Now, right now when I concatenate, I have to select the range and put dividing characters (", ") between each value. The problem is that I cannot get Excel to avoid the null values and just add the names to the concatenated list. What I wind up with is something like the following:
Smith, Brown, Jones, , Fred, , Bob
I would like to get it so that it reads:
Smith, Brown, Jones, Fred, Bob
Now, it's scary, but I am the most literate Excel person in my section and I need to make this as foolproof/hands-off as possible. I know I could have the secretary sort the list for each day and then modify the formula in the calendar to select the range corresponding to the text, but this would confuse her completely.
Anyway, that is my problem, I have tried to clarify it. Please let me know if I have just confused the issue.
Thanks for any help you can give me...
Joe
I have 4 separate Excel Workbook Files. Each file contains between 3 and 8 worksheets. The worksheets contain a yearly calendar showing weekends and holidays. The secretary or supervisor brings up an individual employees sheet, and they use a validation list dropdown box to select vacation days, personal day, etc. The calendar then can, at a glance, show the employees scheduled attendance for the entire year. Now, my boss has the secretary take the data off of these vacation record/attendance sheets and place them on a monthly calendar. This way, he can see the entire month and work coverage at one glance. This activity takes the secretary an inordinate amount of time, because of the constant changes being made with the individual employees vacations. So, what I am trying to do is have a sheet collect the names of all employees scheduled to be off any day of the year. I have it so that I put the dates of the month in separate columns. Under the columns, I have a formula in the cells underneath which goes out to the employee file and checks whether they have any 'V' (vacation) scheduled in the corresponding cell. If a 'V' does exist, then it puts the person name underneath the date column. I have approximately 30 cells underneath each date corresponding to each employee. For example:
November
1 2 3 4 5 6
Smith
Brown
Jones
Fred
Bob
The blanks represent any time the employee sheet shows that the employee referenced in that cell is scheduled to be in that day. Anyway, collecting this data is not my problem.
My problem is that I am using concatenate to take all of the values in each column and am trying to put them into a calendar template so that for any particular date, it will show a word wrapped list of anyone scheduled for vacation that day. Now, right now when I concatenate, I have to select the range and put dividing characters (", ") between each value. The problem is that I cannot get Excel to avoid the null values and just add the names to the concatenated list. What I wind up with is something like the following:
Smith, Brown, Jones, , Fred, , Bob
I would like to get it so that it reads:
Smith, Brown, Jones, Fred, Bob
Now, it's scary, but I am the most literate Excel person in my section and I need to make this as foolproof/hands-off as possible. I know I could have the secretary sort the list for each day and then modify the formula in the calendar to select the range corresponding to the text, but this would confuse her completely.
Anyway, that is my problem, I have tried to clarify it. Please let me know if I have just confused the issue.
Thanks for any help you can give me...
Joe
