TreatLightly
New Member
- Joined
- Mar 31, 2018
- Messages
- 2
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.
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.