LinkBack URL
About LinkBacks
Hello!
I have created a timecard calendar in Excel for teams of therapists working with the same child. It works really well, however, I'm having some data entry errors as the therapists are sometimes entering their "postition" on the team incorrectly (i.e. they enter themselves as a parent, who is not billable, versus a behavior therapist (BT), who is billable).
I have already added a validation rule to the cell which makes them enter a valid position from a list I have created (i.e., them entering "partnet" instead of "parent" is not a problem.)
There are four possible positions on a team: CS, SBT, BT, and Parent.
On one sheet in the workbook I have the team data which would look something like this:
CS
Jane Doe
SBT
Jill Donegood
BT
Jane Doe
Jill Donegood
Parent
Bill Attaboy
Marge Attaboy
I have additional worksheets which contain ranges for each day in a two-week billing interval. On those sheets, our staff enter their name, position, and time range worked. This triggers our billing. For example:
Jane Doe CS 12:00 PM 1:00 PM
This entry would be OK since Jane is the CS as listed above. However, if Jane had listed:
Jane Doe Parent 12:00 PM 1:00 PM
This entry would NOT be OK since Jane is not listed as a parent in the team data list above.
To further complicate things, you can see that the CS and SBT are also listed as BTs on the team data in this example (although this is not true for every team). For example, it would also be OK for Jane to enter:
Jane Doe BT 1:00 PM - 2:00 PM
The best scenario would be if I could have a validation that would only let each member of each team enter a position they are listed under (i.e. Jane can enter CS and BT, but not SBT or Parent).
I don't think I can do that, so I was thinking the next best idea would be to have a conditional format formula that would look at the name that has been entered (Jane Doe), compare it to the position that has been entered (CS) and then check to see if that name is in the team data list under that position (yes, Jane is listed as a CS).
Can someone help me develop that formula?
Thank you!!!
Register To Reply
Bookmarks