Spreadsheet Design Help

Nathan · 1 Apr 2015 at 15:49

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

GAM8LE · 1 Apr 2015 at 15:51

Nathan said:
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

Does this require any automation at all? Like highlighting refresh dates etc?

If not it could be done manually, 1 sheet per shift, each person listed with columns for skills.

Nathan · 1 Apr 2015 at 17:12

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 · 1 Apr 2015 at 21:59

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/.

Nathan · 1 Apr 2015 at 23:01

Thanks for taking the time to help me. I ll let you know how I get on

Agent Smith · 2 Apr 2015 at 06:16

Hi Nathan

if you need some help let me know, I could send you the training matrix I developed for my company with the manual I wrote on how to use it.

It will cover all your requirements and is already completed.

Let me know if I can help.

Shaun

Nathan · 13 Apr 2015 at 09:12

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/.

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?

Nathan · 13 Apr 2015 at 09:23

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.

Nathan · 13 Apr 2015 at 09:55

ok that didnt work too well, im trying to find a forumla to leave blank cells unformatted...

Grrrrr · 13 Apr 2015 at 17:00

I don't have time to check but I think Excel might be treating blank dates as 0. I've not tested this but try setting the conditional formatting to greater than 0 and less than today.

me227 · 13 Apr 2015 at 20:17

Nathan said:
ok that didnt work too well, im trying to find a forumla to leave blank cells unformatted...

Just add another conditional rule for blanks to be white. It'll be the same as what you've just set up but just do "" in the criteria.

Also if you want to be fancy you can do between rules on conditional formatting. Something like between =Today() and =Today()+30, could show a different colour if the date is within the next 30 days. You might need to mess about with the order of the rules too. The blank rule has to be either on the top of the list or the bottom, I can't remember.