Kind of Urgent - Trying to make static schedule populate time ranges based on day of week

[apachedriver64d]

Hi there,

I'm hoping you guys might be able to help. I'm trying to make a visual schedule based on a set weekly schedule that doesn't change (Monday through Saturday). I want to be able to send it out to my peers so they can visually see their direct reports' work schedules (as they vary throughout the day, but stay the same each week). Rather than have to manually fill in these ranges, I'm hoping there is a way to simply input the start time and end time on the second sheet, and have the ranges filled in on the first. Attached is a worksheet I found online, but it's based on the date, rather than the day of the week.

If you get a free moment, I would be tremendously grateful!

[FDibbins]

And what would you want it to look like? Can you manually create a few samples for us please?

[apachedriver64d]

Hi Ford,

Thank you for your reply. If possible, something like this:


Output sheet (visual schedule with populated ranges from other sheet).JPGInput Sheet (start and end times).JPG

I would have up to 15 representatives, along with the other teams (but put just 3 for simplicity's sake), and it would be awesome if the ranges would populate based on the start and end times, and they would be conditionally formatted, without the name of the representative in each cell of his/her row (if possible?).

Sincerely,
MD

[FDibbins]

Your file in post 1 is a bit different to the pic in post 3?

If I understand you, you want to partically transpose your table (times across the top), the use Conditional Formatting to show who works when?

Seeing as you already have all that data in teh WB, could you upload the updated version please?

[apachedriver64d]

FDibbins,

Here is the sheet with what I was working off of (Schedule/Weekly Schedule tabs) and what I'm hoping for it to be, if possible (sheet 1 & 2). Based on the start/end times in sheet 2, sheet 1 would automatically populate the cells in those ranges, based on the day of the week and name. Sorry for the confusion!

-MD

[FDibbins]

Will the list from sheet2 always match the list on sheet1?

[FDibbins]

Looking at the table on sheet2, those are not real times, they are text trhat looks like a time. To fix this, highlight the range of "times", the press CTRL H (Find/Replace), Find "AM" replace with " AM" replace ALL, repeat with PM. Leave out the "". You should now hqve a table of times - you should see that that the went from left-aligned (default text alignment) to right-aligned

If I understand what you want, correctly, remove all CF and other formats and contents from the table on sheet1, you should be left with a blank table.
Your profile indicates 2007. CF cannot refgerence other sheets on 2007, so copy (or reference) the table from sheet2 onto sheet1 - I put it in U2

1. highlight the range you want to apply the conditional formatting to (C3:P20 in your sample)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =AND(C$2>=$W3,C$2<=$X3) Format fill as needed

Hopfully I understood what you wanted

[apachedriver64d]

Hi FDibbins,

I actually have 2013. I'm not sure I fully understand what you're saying in your reply. Would you be able to take my document, and at least start it--so then I can copy down and over? I understand what you're saying about time, but I'm not sure how to get the ranges on sheet 1 to reflect the start/end times (and in between) on sheet 2.

[apachedriver64d]

Basically, I'm hoping to take the current sheets(Weekly Schedule/Schedule) and make them look like sheet 1 & 2, but by using the formulas already in place. However, the formulas populate the cell ranges based on the date. I simply want to change them to populate ranges in Weekly Schedule sheet from the start/end times listed in Schedule sheet, based on day of the week.

Would you be able to change formulas in Weekly Schedule so that they simply refer to day of week, instead of date (the schedule would stay the same week to week?

I appreciate your time. I'm not as proficient at Excel as I'd like to be.

Sincerely,
MD