New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Complex car parking roster, looking for help to avoid human error

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Complex car parking roster, looking for help to avoid human error

Postby Hkb80 » Mon Jun 20, 2016 8:02 am

My apologies now for this may be rather complex.
At work we have a car parking roster which involves 7 reserved spaces and 7 casual spaces.
The 7 reserved spaces are used mainly by the same 7 people (upper management). Unless they are away and then their space is made available to the roster.
Of the 7 casual spaces, 4 senior staff have preferential treatment. Unless they are away and then their space is made available to the roster.
The remaining 3 casual spaces are shared amongst general staff who have requested to be on the roster. At the moment there are 12 general staff on the roster, each with their own unique requirements for parking preferences (ie part timers and full timers).
The roster is allocated over a 2 week period with available bays as fairly allocated amongst all 23 staff as possible. As you can imagine, a bit of a planning nightmare (and it isn't even my job!)
The roster is currently in an excel spreadsheet and uses 3 tables to track availability of car park spaces. And then one table to allocate what is available to the 12 general staff.
I am hoping to get some suggestions as to how we can use formulas, etc, to make this process a little/lot easier. I have a basic understanding of formulas but i am not even sure where to start!
I have attached the spreadsheet. I look forward to hearing from you!
  • 0

You do not have the required permissions to view the files attached to this post.
Hkb80
Rookie
 
Posts: 3
Joined: Jun 20, 2016
Reputation: 0
Excel Version: 2010

Re: Complex car parking roster, looking for help to avoid human error

Postby gebobs » Thu Jun 23, 2016 10:02 am

Let me just start off by saying...WHAT THE ACTUAL ****!!!! What a bunch of prima donnas! Let me guess. Are you guys in the UK? LOL

I'm sure there are lots of things we can do to automate this. For starters, you can automate the dates. Enter the first date in the leftmost date column on top and have the rest calculated.

Also, we can automate the day counts in the top table (column N). I see that the % allocation is based on the number of days requested which varies from person to person. I suggest breaking this data out so that it is easier to edit and so the calculate formulas will be consistent.

I assume a slash through a cell in the top table indicates that the employee will not need a space/will be away for that specific date, correct? These are the days for which they have not requested a space. Rather than putting the slash border in, perhaps you could mark those days with an X. That way we can automate the counting.

I see that if a space is marked "Yes" among the bottom two tables, that means it is available for allocation in the top table. These Yes/No can be semi-automated with data validation and the Yeses highlighted with conditional formatting.

What else do you want it to do? It seems to me that it is going to otherwise be an inherently manual process that poor Angela is going to have to manage for the little lords and ladies.

I've taken this first crack at it. You can see if you like it or not. We might be able to do some other things.
  • 0

You do not have the required permissions to view the files attached to this post.
gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Complex car parking roster, looking for help to avoid human error

Postby Hkb80 » Sun Jun 26, 2016 12:34 am

Thanks for the reply gebobs :) and no we aren't in the UK

How would I go about automating the counting in column M?

And yes your assumptions were correct regarding spaces not requested, and spaces available. Also that Angela loathes doing this roster every fortnight!

Top table: I had been wondering if there was some way to create a selection list for each column that was based on only the available spaces for that day. For example:
day 1 (column C) the spaces available are: C198, C200, C201, C202.
day 2 (column D) the spaces available are: C195, C198, C200, C201, C202.
But then the selection list would also update once a space had been allocated and remove it from selection. Or perhaps some conditional formatting to flag if a space had been allocated twice on the same day.

Is that a bit 'pie in the sky'?
  • 0

Hkb80
Rookie
 
Posts: 3
Joined: Jun 20, 2016
Reputation: 0
Excel Version: 2010

Re: Complex car parking roster, looking for help to avoid human error

Postby gebobs » Mon Jun 27, 2016 11:22 am

The automated counting of M is easy. I meant to include it in the first file but it is in this one.

As for the drop-down data validation for the top table, I have been thinking about how this could be done. At this point, it's eluding me. I'll give it some more thought and maybe try to recruit other eyes to give it a look see.
  • 0

You do not have the required permissions to view the files attached to this post.
gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Complex car parking roster, looking for help to avoid human error

Postby Hkb80 » Thu Jul 07, 2016 7:47 am

Any luck gebobs?
  • 0

Last edited by pecoflyer on Thu Jul 07, 2016 9:53 am, edited 1 time in total.
Reason: Remove unnecssary quotes
Hkb80
Rookie
 
Posts: 3
Joined: Jun 20, 2016
Reputation: 0
Excel Version: 2010

Re: Complex car parking roster, looking for help to avoid human error

Postby gebobs » Mon Jul 18, 2016 3:33 pm

Hkb80 wrote:Any luck gebobs?


Negative.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 24 guests