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