Spreadsheet Design Help

Nathan

Nathan

Nathan

Soldato
Joined
4 Feb 2003
Posts
5,375
Location
West Midlands
Hey all,

I am kind of the SPOC for IT on my shift at work (Police)

My boss has asked me to assist her in designing a spreadsheet displaying skills of individuals.

I have not touched Excel since I left school however i was fairly savvy with it. Basically i need to have 5 seperate shifts, with approximately 10 bobbies on each shift. Then each bobby i need the skills listed and when each skill requires a refresh on a specific date. Any help would be great :)
 
No we have a database for that however she has been asked to look at current training needs and wants to collate up to date information. Sounds good, I ll see what I can put together on Friday for her :)
 
Grrrrr said:
It's hard to be too much help without actually seeing the data but my first thought was similar to Gamble's. Names down the left, skills across the top.

Rather than splitting the shifts into different sheets, though, I'd be incline to keep it all on the same sheet and use filters

Code: 
Shift   Bobby   Skill1 Skill2 Skill3 Skill4
  1       A       x      x      x      x
  1       C              x             x
  1       F                     x      x
  1       N       x      x             x
  2       A       x      x      x      x
  2       F                     x      x
  2       P       x                     
  3       P       x                     
  3       J              x      x      x
  3       F              x      x      x
  3       N       x      x             x

You could then filter on shift 2 to see all the bobbys for that shift, or filter on a bobby to see all the shifts they're working (or filter a skill to see which bobbys have it, etc etc).

For skill expiry dates you could duplicate the table into a different sheet but instead of crosses (or ticks) you put the expiry date. Then in the first sheet you could use conditional formatting to highlight skills that expire soon (e.g. Red expire within a month, orange within 3 months, or green longer)

One final thing I forgot to write, there are an awful lot of Excel advice website on the web, but this one I find brilliant http://chandoo.org/wp/.
Click to expand...

Just used this design with filters and its great, easy to use for the inspector as she is not great with the software.

One question, instead of the X in the skill cells im going to use the expiry date of the skill. Is there a way to automatically highlight the cell if the date is older than the current date so she can easily see out of date skills?
 
Should have googled :) used the below and worked a treat.

Select cell and go to Format|Conditional Formatting

Select: Cell Value Is >> Select: Is Less than >> Enter: =Today()

Click Format..and choose colour from Pattern Tab.

Thanks for the help guys, this forum is great for sharing knowledge.
 
You must log in or register to reply here.
Back
Top Bottom