Pointing formula group to new references... I think that's what I want to do...

[TreatLightly]

Hi all,

It's my first time posting here, and I apologise for what will surely be a question full of incorrect vernacular and confusion. I will however be concise.

So: I'm building a roster for event staff - it should be simple, but I got carried away. The first sheet (Schedule)is where start and finish times are entered, and it calculates hours and costs and all that business... here's an image.

The second sheet (Visual)is where I get carried away - it takes the data from the Schedule sheet and creates a visual representation of each crew member's hours across the working day. This will be the printing copy for each event day, to provide an easy reference for others to see who is and isn't on shift. Like this.

It's perhaps a little over the top, but I was in the mood for a challenge. Then I got stuck.

The problem is that it took me a long while to build the formulae to make the Visual sheet work (lots of IFs and some conditional formatting), and yet it only references the first day of the overall schedule on the Schedule sheet (there are 4 weeks in total). I'm wondering if there is an efficient way to "point" the Visual sheet's group of formulae to different days in the Schedule sheet, so that I can quickly choose which day is visually represented at any one time. Does that make sense?

I'm hoping the answer to this is either very simple or it's a fun challenge... unfortunately I've reached the end of my very limited excel knowledge, and research into a solution has got me nowhere. A link to the spreadsheet is here: (in google drive) and the password to the protected cells is "pass". Thanks in advance for anyone having a crack at this!

Cheers,

Matt.

 

[thisoldman]

I am so accustomed to Excel dates and times I had to use them in the Schedule entries. I also only used eight crews. My workbook can be downloaded from https://www.dropbox.com/s/k5mgpuraeh4fxi9/treatlightly_gantt.xlsx?dl=0

The Schedules sheet:

I copied the times from Schedule to Visuals, while at the same time I subtracted three hours from those times—'3/24' is three hours to Excel.

	B	C	D	E	F	G	H	I	J	K	L
Week 1	Week 2	Week 3	Week 4
Crew 1
Crew 2
Crew 3
Crew 4
Crew 5
Crew 6
Crew 7
Crew 8

<tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #EBEBEB"]For date[/TD]
[TD="bgcolor: #FFF5D7, align: right"]18/06/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #EBEBEB"]Week Number[/TD]
[TD="align: right"] 3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBEBEB, align: right"]Start[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Finish[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Start[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Finish[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Start[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Finish[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Start[/TD]
[TD="bgcolor: #EBEBEB, align: right"]Finish[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6:00[/TD]
[TD="align: right"]14:00[/TD]

[TD="align: right"]9:00[/TD]
[TD="align: right"]19:00[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]23:00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7:00[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]4:00[/TD]
[TD="align: right"]9:00[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9:00[/TD]
[TD="align: right"]19:00[/TD]

[TD="align: right"]7:00[/TD]
[TD="align: right"]13:00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9:00[/TD]
[TD="align: right"]19:00[/TD]

[TD="align: right"]17:00[/TD]
[TD="align: right"]23:00[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]17:00[/TD]
[TD="align: right"]23:00[/TD]

[TD="align: right"]9:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]7:00[/TD]
[TD="align: right"]13:00[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4:00[/TD]
[TD="align: right"]9:00[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]4:00[/TD]
[TD="align: right"]9:00[/TD]

</tbody>

Visuals


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=WEEKNUM(GanttDate - 1) - 22[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=IF(Schedule!C6 = "", "", (Schedule!C6 < 3 / 24) + Schedule!C6 - 3 / 24)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]=IF(Schedule!D6 = "", "", (Schedule!D6 < 3 / 24) + Schedule!D6 - 3 / 24)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]GanttDate[/TH]
[TD="align: left"]=Visuals!$C$4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In C5 I corrected for my week beginning on Sunday by subtracting 1 from the date. I found which section of the table to use by subtracting 22 from the WEEKNUM.

I set up the Gantt chart by formatting the cells with a custom number: ";;;" (three semicolons, no quotation marks). This stops any entries from showing in the worksheet or printing, but the entries still appear in the formula bar. This does not affect the conditional formatting: a fill color.

Visuals sheet

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O8[/TH]
[TD="align: left"]=REPT(O$7, AND(ROUND($N8 - 3/24, 6) >= IFERROR(ROUND(INDEX(Weeks, MATCH(O$7, $D$9:$D$16, 0), 1, WeekNumber), 6), 0),
ROUND($N8 - 3/24, 6) <= IFERROR(ROUND(INDEX(Weeks, MATCH(O$7, $D$9:$D$16, 0), 2, WeekNumber), 6), 0)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=N8 + TIME(0, 30, 0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]WeekNumber[/TH]
[TD="align: left"]=Visuals!$C$5[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Weeks[/TH]
[TD="align: left"]=(Visuals!$E$9:$F$16, Visuals!$G$9:$H$16, Visuals!$I$9:$J$16, Visuals!$K$9:$L$16)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The named formula, Weeks, has four areas, each of eight rows and two columns.
=( range_1, range_2, range_3, range_4)

I used INDEX formulas in the chart to do the lookups:
INDEX( lookup_range, row_to_lookup, column_to_lookup, area_to lookup )

I threw an IFERROR around the INDEX to correct for the unsuccessful lookups. Everything worked except for the comparison at 02:00. I found I was comparing two floating point values that differed at some decimal digit. I tried rounding to six places gave me the proper correction for the floating point error and I did not try to fine-tune the rounding. I was happy with a first-guess success.

The AND evaluates to TRUE or FALSE. Within the REPT formula, TRUE is coerced to 1 and FALSE to 0.
=REPT( string, n_times )
The whole formula tells Excel to repeat the crew name one time if the AND evaluates to TRUE and to repeat the name zero times if the AND evaluates to FALSE.

In Visuals!O8:V48 I used conditional formatting, assigning each crew name to a different color.

 

[TreatLightly]

Hi thisoldman,

I'm speechless - thank you so much for your work here... I can't wait to have some time to properly look over just how you've done this! You're incredible!!!

 

[thisoldman]

You're welcome. I'm glad to help.

I don't think I explained what I did in the workbook well enough. If you have any questions, post them and I'll get back to you.

 

[dannych]

Sorry for bringing back this somewhat old thread but I had a question for the solution you've posted.

Why subtract 3 hours from the times? I have a similar roster (here) I'm making (which lead me this is wonderful site) and it is similar to OP, however, some of my "Crew"'s Times begin during the night and finish the morning after e.g. Crew 1 - Start: 23:00, Finish: 07:00.

I think my issue stems from the 3 hour subtraction since currently, I have no time alterations. Adding the alteration passes the first iferror condition but fails the second iferror condition. Additionally, my time scale is from 6:00 to 7:00 the following day with 30-minute intervals.

Everything else works perfectly, just those "Crew members" that work at night do not show up. Any help in this regard is welcomed.