Theme Park Training Records

D

Dr. Compynei

Hi All,

I don't work with Excel all that often, and its been some time since it was
involved in my Computing A-Level!

The scenario for the problem I have is this:

I work in the theme park industry, in the Rides Management Team. The system
for training a ride operator is somewhat over complicated, and we are hoping
to move across to excel to help us better manage our team of around 80
staff.

When an operator is trained on a ride, they are listed on a competent
operator form twice, by hand, one kept on the ride, one kept in the office.
This contains the following information: Ride, Operator column for the name
to be listed, date trained and who they were trained by.

The date trained is then added to a spreadsheet in Excel, basically so we
can keep an eye on where staff are trained, and where we can best place
staff each operational day. Other aspects of the training process obviously
come in, such as ride training acknowledgements and operator assesments,
however these are kept outside of this system.

Basically, what I am trying to create is the following:

An excel workbook, which has a table of staff names running down one side,
and then ride names running across the top, thus allowing dates to be
inserted for various rides.

I then want a sheet for each ride, which will be a competent operator form.
So for example, Ride A will have its own sheet, we will pick staff names
from a dropdown box, enter the date they were trained and who by. This will
then cross reference into the matrix sheet, which will show the date they
were trained, in the correct ride column, by their name.

The problems I'm having at the moment is:
- Getting staffs names into a dropdown box
- Finding a formula that will look up when someone has been added, and place
the date.

I realise this is alot of info, and is probably quite hard to understand
unless you know the training model of the park, I am happy to post my work
if this helps anyone. I also do realise that this would probably be more
suited to excel, but due to the varying IT skills in the department of
employees, and all of them knowing excel (well, working with it), this seems
the only solution to us keeping better records, with less hassle.

Many thanks.
 
D

Dr. Compynei

Dr. Compynei said:
Hi All,

I don't work with Excel all that often, and its been some time since
it was involved in my Computing A-Level!

The scenario for the problem I have is this:
--snip--
I know, bad form to reply to my own post, but after some playing around with
this, I think I have a solution, all except one problem.

What I want to do is this:

Look on Sheet1 at a list of names.
Compare with Sheet 2
When a match is found in sheet 2 (say for example the match is in A27)
I then want to return the value from Sheet 2 E27 back into Sheet 1.

Any ideas?

Many thanks!
 
S

sbitaxi

--snip--
I know, bad form to reply to my own post, but after some playing around with
this, I think I have a solution, all except one problem.

What I want to do is this:

Look on Sheet1 at a list of names.
Compare with Sheet 2
When a match is found in sheet 2 (say for example the match is in A27)
I then want to return the value from Sheet 2 E27 back into Sheet 1.

Any ideas?

Many thanks!

What you are trying to do is more of a database function and reports.
You can do it in excel, but it is a lot of redrawing of information.

You can do it using conditional mail merges in Microsoft Word into a
template for the individual rides (Your Competent Operator Form) that
way you are not redrawing it 100 times or bogging your workbook down
with a lot of cumbersome formulae.

You should only need 1 sheet for this and one or two Word templates
for the merges.
Staff in a column, rides in a row. I'll have to think the merge
through a little and get back to you. It's a start.


Good luck!

Steven
 
P

Pete_UK

Assume the name you are looking for is in A2 of sheet2, then put this
in B2:

=VLOOKUP($A2,Sheet1!A:E,5,0)

and it will return the corresponding value from column E of sheet1.
Copy down as required.

Hope this helps.

Pete
 
D

Dr. Compynei

Pete_UK said:
Assume the name you are looking for is in A2 of sheet2, then put this
in B2:

=VLOOKUP($A2,Sheet1!A:E,5,0)

and it will return the corresponding value from column E of sheet1.
Copy down as required.

Hi Pete

It doesnt seem to work, let me try and explain a little more clearly.


Ride Table

RideName
Sue Trainer Date
Dave Trainer Date
Phil Trainer Date

In the Matrix Table I then want the following

Name Ride1 Ride2 Ride3 Ride4 Ride5
Sue Date
Dave Date
Phil

So formulae, for example in Sue, for Ride3, should find her name in the Ride
Table, and return the date from that row.

Does this make it any clearer?

Many thanks all
 
P

Pete_UK

It would be easier to have another column with the ride name in, so
that Sheet1 lists all employees in any order for each ride that they
have been trained in. Something like this:

Name Trainer Date Ride
Sue A 01/01/08 Ride1
Dave A 02/01/08 Ride1
John B 03/01/08 Ride2
Sue B 04/01/08 Ride3
Phil A 05/01/08 Ride1

Then in Sheet2 with the layout you showed, you can put this formula in
B2:

=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$D$2:$D$6=B$1)*Sheet1!$C
$2:$C$6)

Apply conditional formatting to this cell such that if the cell value
is zero then choose a foreground colour of white. Format the cell
normally to show a date in the style you prefer. Then copy this
formula across and down, and with the test data above you will end up
with this:

Name Ride1 Ride2 Ride3 Ride4
Sue 01-Jan-08 04-Jan-08
Dave 02-Jan-08
Phil 05-Jan-08
John 03-Jan-08

You must ensure that the names and training details on Sheet1 only
appear once for each person and ride for this to work.

To get your individual lists for the rides, just apply autofilter to
column D in Sheet1.

Adjust the ranges to suit your data.

Hope this helps.

Pete
 
D

Dr. Compynei

Pete_UK said:
It would be easier to have another column with the ride name in, so
that Sheet1 lists all employees in any order for each ride that they
have been trained in. Something like this:

Name Trainer Date Ride
Sue A 01/01/08 Ride1
Dave A 02/01/08 Ride1
John B 03/01/08 Ride2
Sue B 04/01/08 Ride3
Phil A 05/01/08 Ride1

Hi Pete,

That is a help, and it would make things alot easier, however each competent
operator form has to be kept independant. Thats a big part of the spec, and
so that we can just print Ride1s competent operator form off, as an updated
hard copy needs to be displayed on that ride.

Any ideas on how I could implement the original solution?

Many thanks
 
P

Pete_UK

Maybe you missed this bit in my earlier response:

"...
To get your individual lists for the rides, just apply autofilter to
column D in Sheet1.
... "

You can then print the sheet, and only what is visible will be
printed.

If you specifically want a list of trained operators for each ride
(perhaps in a separate sheet), then you can apply the filter for each
ride in turn, highlight the visible rows and then <copy> just those
rows to a new sheet. This will not be automatic, although if you send
me an example of your workbook to:

pashurst <at> auditel.net

(change the obvious) then I can set it up to be so.

Hope this helps.

Pete
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top