Nursing Scheduling workbook

D

Dan E

Hi - not sure if this is the best place to post this, but here goes. I'm
using Excel 2000. I have been asked by my wife, who has responsibility for
this, to look at a pre-existing workbook whose object is to schedule nurses
and technicians in a 70+ bed nursing facility, for specific shifts, for each
day in a fortnightly period. Since the 70+ beds will soon become 220+ beds,
I want to get this workbook working well, but am struggling at the moment.
Each shift has a code associated (e.g. R, or TCA, or 4L etc.), corresponding
to a particular shift in a particular area of the facility. Different
shifts have different numbers of work hours associated, and those hours can
be looked up in another sheet - currently the codes are not sorted
alphabetically. The person who fills out the roster does so on one sheet
(POST), trying to take into account the needs and wants of the individual
before assigning the shift - one nurse may not be able to work Tuesday days
or Wednesday nights or Saturday all day, for example. When the shift
allocations are made in the shift entry sheet (named POST), the shift code
is copied to a corresponding cell in another sheet (named UNIT 1) using e.g.
"=+POST!C6". To complicate matters a little, each code may also appear with
RO appended (so they become RRO, or TCARO, or 4LRO etc.), meaning that the
person is on that shift for training purposes - the budgeted hours for
training are separate. Next to the cell where the shift code is imported
into UNIT 1, there are two columns - Hours and OR hours, where, currently,
the hours for a work shift or a training shift respectively are entered by
hand. The first thing that struck me was that there had to be a way of
using the code in the UNIT 1 cell representing the shift to look up the
hours associated with that shift, then entering the result of the lookup in
the correct (Hours or OR hours) cell, thus saving a lot of manual lookup and
entry, both being prone to error. I've browsed the web and and an Excel 97
book from Microsoft ("Running Excel 97") without getting more than a glimpse
of some functions that MIGHT be used to do what I want - but I don't know
how to do it! If anyone could please help with advice or a pointer to
examples of what I want to do on the web, I'd be extremely grateful - I'm
NOT an Excel expert by any means. Of course, I then want to go and try to
balance budgetted hours against actual hours. I know the basics of using
Excel for financial stuff, but a lookup involving finding a text string in
one column then looking to see if the string ends in OR, then as the
solution to the lookup retrieving text from another column next to the
lookup column, then using the result of OR or not OR as ending to the code
to put the hours in the correct column in UNIT 1 is beyond me at the moment.
One final thing, when no shift is allocated, I need to insert a null value
represented by a hyphen in that cell, and also where no hours are allocated
to a person in UNIT 1 under Hours or OR hours, I need to put a hyphen also.

As an alternative, I'd be more than willing to look at a freeware or
shareware or low-cost ready-made solution.

Sorry to be so long-winded. I am able to post an example of the workbook to
my web site if that would help.

All help most gratefully received.

Dan E
 
B

BrianB

Ok - you have done phase 1 in the process by outlining your needs, bu
all it does is show your general confusion.

First, define you goal. What are you trying to achieve ? It is a goo
idea to decide on some destination before starting the journey, even i
you have to divert a bit later.

Try to take a "database approach" if you can. ie. keep all your data i
a single table. No need for too much formatting here. Just colum
headings in row 1 and data underneath. Perhaps this is your goa
anyway. This enables use of the more powerful Excel functions to do
lot of hard work fast.

The next step is to break down the process into small steps and then t
put them into the correct order. Where does it start ? It seems tha
this is by someone filling in a form. This should be a worksheet t
save later data transfer. If possible/necessary , make provision fo
these entries to be collated into a single table.

What data is required to complete the form. eg .Lookup tables.

Do you then just need to collate various forms, or are furthe
decisions required before the finished product is calculated ?

You can get a lot of help from this forum. To get the best, limit you
message to a single problem. Not only are you more likely to get
reply, you are more likely to get the right one. None of us here know
"all the answers." It is probable that a high percentage of you
questions will never reach publication - you would think of the answe
as you write.

Good luck
 
D

Dan E

Thanks Brian - I'm not really confused, nor am I new to problem solving
technique - it's simply that I have to work with an inherited workbook that
was issued at the corporate level, and I don't have sufficient knowledge of
the various Excel functions available for text search and manipulation to
describe what I need to do in technical language - I'm a physicist, not an
Excel guru. I realized that I was asking too complex a question, and I've
re-phrased the first step (just posted) in the Excel newusers forum as
"Newbie - help needed with lookup, please." If you can help, I'd be
extremely grateful.

Dan E
(e-mail address removed)
 
D

Dan E

Thanks Brian - I'm not really confused, nor am I new to problem solving
technique - it's simply that I have to work with an inherited workbook that
was issued at the corporate level, and I don't have sufficient knowledge of
the various Excel functions available for text search and manipulation to
describe what I need to do in technical language - I'm a physicist, not an
Excel guru. I realized that I was asking too complex a question, and I've
re-phrased the first step (just posted) in the Excel newusers forum as
"Newbie - help needed with lookup, please." If you can help, I'd be
extremely grateful.

Dan E
(e-mail address removed)
 
D

Dan E

Thanks Brian - I'm not really confused, nor am I new to problem solving
technique - it's simply that I have to work with an inherited workbook that
was issued at the corporate level, and I don't have sufficient knowledge of
the various Excel functions available for text search and manipulation to
describe what I need to do in technical language - I'm a physicist, not an
Excel guru. I realized that I was asking too complex a question, and I've
re-phrased the first step (just posted) in the Excel newusers forum as
"Newbie - help needed with lookup, please." If you can help, I'd be
extremely grateful.


Dan E
(e-mail address removed)
 

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