Ok, my penchants for making things more complicated than they need be, and for completely overlooking the obvious seem to have gotten me in a bind that has me doing a bit of 
.
I'm trying to make a scheduling spreadsheet for work. The previous method was archaic and troublesome beyond belief. My department has around thirty people. Each person will be assigned to one (or more, usually more) of approximately fifteen tasks. The manager used to do all the assigning and checking completely manually. The completed schedule was typed into a simple table, days of the week across the top, employees down the side. This process which usually resulted in several tasks being overlooked on any given day, and made it difficult to figure out who was being assigned to any given task on any specific day. In my madness I suggested that it would be possible to have Excel (2K at work, 2K3 at home) do the checking and formatting, etc. automatically.
I've only ever been an extremely casual user of Excel, and all my knowledge is self-taught. So I'm sure that there is a much more elegant way of dealing with all the kludges that I've worked out. Any volunteers on that front won't be turned down. But that's not the reason I'm actually posting. You see, I'm stuck. I've gotten most of the functionality I need/want, but a key point is eluding me totally.
(Giving up on posting the workbook, as it's entirely too complicated to type by hand, and the HTML maker seems to give me too many characters to make it work right. If anybody really needs to look at the actual file, I can e-mail it.)
Basically, I have one sheet with the names of the employees (first, last, concatenated to full name), start times (per day of the week), and stop times all arranged in a table (each item is it's own column). Also on the page (a couple of columns over) I have a list of tasks to be performed. Both of these settings are defined by named ranges, allowing for dynamic growth.
Sheet 2 has the actual schedule, more or less thusly:
etc. Each task cell is a dropdown using data validation of the tasks given in the named list on the data page. Employee names are pulled from the data page also. Each block of rows that makes up a group of tasks for a day is a defined named range also (Monday, Tuesday, etc.)
Sheet3 is simply a list of the tasks in column A, and the days of the week in columns B through F. I used the formula:
=COUNTIF(Monday,$A2) (for some reason the INDIRECT function wouldn't work to retrieve the name of the range to use from the column heading, so I had to type them all in manually)
to place a count of the people assigned to each task in the appropriate slot in the grid. I used conditional formatting to highlight where each task was inadequately staffed.
Sheet4 is my listing by users. I have a column of employees (pulled from the named range) (the header of this column is two cells merged to keep in line with the other columns), and a column for each day of the week (the headers for these columns each have two cells, one for the name of the day, one for the date). In the table data cells, I kludged together the formula:
=INDEX(Monday,1,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,2,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,3,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,4,MATCH($A3,INDEX(Schedule,1,),0))
(again, the INDIRECT function wouldn't seem to work) to pull the data from the named range based upon the name of the employee in the first column. Looks pretty bad, has lots of extraneous ", "'s, but I have no idea how to make it prettier, and it more or less works...
Sheet5 is my major stumbling block... (And the reason I wrote this post in the first place.) I'd like to have a column with the name of the task, and a column for each day of the week (same heading structure as found on sheet4). The data should consist of a list of all persons assigned to the given task.
I got a little ways into it by making named ranges based upon each day in sheet4 (monuser, tueuser, etc), and the following formula:
=INDEX(User,MATCH("*"&$A6&"*",MonUser,))
but that only returns the first person found. I've already invested fifteen or so hours total in just getting this far (I didn't even know about named ranges, much less dynamically expanding ones when I started), and have been beating my head for the last couple of them on this problem.
Our IST dept is extremely draconian, and there's a pretty decent chance of getting reprimanded for suggesting the addition of any outside programs/addins, so I'd much rather keep with what's already included with Excel whenever possible.
Any help would be greatly appreciated!!
Thanks!
-me.
.I'm trying to make a scheduling spreadsheet for work. The previous method was archaic and troublesome beyond belief. My department has around thirty people. Each person will be assigned to one (or more, usually more) of approximately fifteen tasks. The manager used to do all the assigning and checking completely manually. The completed schedule was typed into a simple table, days of the week across the top, employees down the side. This process which usually resulted in several tasks being overlooked on any given day, and made it difficult to figure out who was being assigned to any given task on any specific day. In my madness I suggested that it would be possible to have Excel (2K at work, 2K3 at home) do the checking and formatting, etc. automatically.
I've only ever been an extremely casual user of Excel, and all my knowledge is self-taught. So I'm sure that there is a much more elegant way of dealing with all the kludges that I've worked out. Any volunteers on that front won't be turned down. But that's not the reason I'm actually posting. You see, I'm stuck. I've gotten most of the functionality I need/want, but a key point is eluding me totally.
(Giving up on posting the workbook, as it's entirely too complicated to type by hand, and the HTML maker seems to give me too many characters to make it work right. If anybody really needs to look at the actual file, I can e-mail it.)
Basically, I have one sheet with the names of the employees (first, last, concatenated to full name), start times (per day of the week), and stop times all arranged in a table (each item is it's own column). Also on the page (a couple of columns over) I have a list of tasks to be performed. Both of these settings are defined by named ranges, allowing for dynamic growth.
Sheet 2 has the actual schedule, more or less thusly:
Code:
day of week employee employee2 employee3
Monday hours hours hours
Montask1 task task task
Montask2 task task task
Montask4 task task task
Tuesday hours hours hoursSheet3 is simply a list of the tasks in column A, and the days of the week in columns B through F. I used the formula:
=COUNTIF(Monday,$A2) (for some reason the INDIRECT function wouldn't work to retrieve the name of the range to use from the column heading, so I had to type them all in manually)
to place a count of the people assigned to each task in the appropriate slot in the grid. I used conditional formatting to highlight where each task was inadequately staffed.
Sheet4 is my listing by users. I have a column of employees (pulled from the named range) (the header of this column is two cells merged to keep in line with the other columns), and a column for each day of the week (the headers for these columns each have two cells, one for the name of the day, one for the date). In the table data cells, I kludged together the formula:
=INDEX(Monday,1,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,2,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,3,MATCH($A3,INDEX(Schedule,1,),0))&", "&INDEX(Monday,4,MATCH($A3,INDEX(Schedule,1,),0))
(again, the INDIRECT function wouldn't seem to work) to pull the data from the named range based upon the name of the employee in the first column. Looks pretty bad, has lots of extraneous ", "'s, but I have no idea how to make it prettier, and it more or less works...
Sheet5 is my major stumbling block... (And the reason I wrote this post in the first place.) I'd like to have a column with the name of the task, and a column for each day of the week (same heading structure as found on sheet4). The data should consist of a list of all persons assigned to the given task.
I got a little ways into it by making named ranges based upon each day in sheet4 (monuser, tueuser, etc), and the following formula:
=INDEX(User,MATCH("*"&$A6&"*",MonUser,))
but that only returns the first person found. I've already invested fifteen or so hours total in just getting this far (I didn't even know about named ranges, much less dynamically expanding ones when I started), and have been beating my head for the last couple of them on this problem.
Our IST dept is extremely draconian, and there's a pretty decent chance of getting reprimanded for suggesting the addition of any outside programs/addins, so I'd much rather keep with what's already included with Excel whenever possible.
Any help would be greatly appreciated!!
Thanks!
-me.
If it can't be done with standard functions, a UDF (User Defined Function, right?), or some VBA code would be fine, as long as it's something I can cut and paste...
