Solved: Template??? Conference Matrix

[stanl]

I just inherited a task of setting up an Excel sheet to cover a 4-day Annual meeting where I work. I was given what was created last year - very pretty but I understand a nightmare to update (beaucoup last minute changes)

I decided it might be be efficient to set up a series of validation lists for Staff, day, start, end, task and event on a hidden sheet, then use these to create a Master sheet with validated drop-downs to set up and alter the overall schedule, so an entry might look like

Staff Day Start End Task Event
Bob Day1 7:30AM 9:AM Badge Check Registration

and if Bob can't make it last minute, then assign the task to Joe.

Then I was thinking of 4 sheets, one for each day initially set up on 1/2 hour intervals

7:30 8:00 8:30 9:00 9:30 10:00..............................
Event Registration Session 1 Lunch

Bob Check Badges Check Badges
Mary Register
Ed Register
Tom Kick-off Speech

with everything cleared under the event line, then updated via a Button or macro to fill in the Staff names, and tasks under the appropriate hours per event per day.

I'm hoping there might be a template out there to help with this second part. Would be eternally grateful.: pray2: Stan

[mdmackillop]

Hi Stan,
Can you post your workbook as a starting point?

[stanl]

Hi Stan,
Can you post your workbook as a starting point?

just remember... I inherited it. There is a hidden sheet named lookups, my idea is to get all possible values then create the Master worksheet. I'm not thrilled about the design of each Day [there will be 4, I included 1] as I would rather have time go across, people down. Anyway, each 'Day' needs to start as a blank 'template' with only the events [in blue] outlined. As last minute changes are made as to who is assigned to what I need a way to clear the insides of the Day sheet, iterate through the master sheet and block out in grey the tasks by time by person throughout the events, for printing and distribution. There will also be a need for small summaries, viz the number of persons conducting registration, or assisting vendor booths. Ideally, I would create 4 buttons and each would reference a day sheet clear the task(s) range, and re-format according to the specs in the master worksheet.

I was thinking about creating vlookups for staff and times to return the intersection of the row,col for tasks... but I'm just not good making pretty
:banghead: Stan

[mdmackillop]

Hi Stan,
Here's a quick colour assignment system. Does it meet any of your needs?

[stanl]

Associating color w/tasks makes sense. My alternative is to associate each Person with a row/ time with a column, so that iterating through the master worksheet I can

0. [clear all previously set task ranges]
1. select the sheet for the day
2. parse out the Range and task for each person via vlookups
3. Format those cells with text [and maybe color]

Thanx for your suggestions. Stan

[mdmackillop]

Here's a simple userform alternative for adding the tasks to selected cells, for your consideration.

[stanl]

Thanx, I'll mark this solved.