PDA

View Full Version : Prompt/Store value based on a selection/entry



stltom554
01-30-2016, 11:36 AM
Greetings.

I have created a new schedule for the police department I work at. I had to replicate the old schedule, which was in table format in Word so I was somewhat limited.

The redesign included the following changes:



Restricting entry to make all the schedules (4 Platoons) uniform
To make the color formatting uniform (using conditional formatting)
To add automated calculations to make sure officers fall within 160 hours, as well as total Comp, Vacation, etc. for our payroll coordinator.
The ability to populate 14 schedules in one Excel file as compared to 14 Word schedules for each platoon x4.



My ultimate goal is to tie in our 28 day sheets to read, and populate them automatically based on the schedule.

(Yes. I know this application would be better served if it were web based front end with a SQL db, or even an Access db, but the command staff does not want to waste the money on such 'nonsense' so I had to stay in Excel)

My current problem is with Vacation Days (V), Sick Leave (S/L), and Comp (C). There are several different hours that can be associated with these days. Most everything is based on 12, 8, or 4 hours, but let's say I have a doctors appointment that only takes me off the road for 2 1/2 hours. Then I need to be able to enter 2.5. My drop downs are based on a value list. To add S/L 1, S/L 2, etc. will make the list even more congested than it already is.

Is there a way to use VB to look at the entry and if the user enters S/L from the drop down, a window pops up and asks "How many hours of S/L would you like to enter?" Then the user enters the hours which is stored in the field. I have been searching and tried many options but I just can't seem to get it and we are trying to get the new forms in play.

If anyone could help me, I really would appreciate it. Thank you for your time.

Also, if you see a better design please let me know. I know my way around Excel but I am obviously no master. There are a lot of formulas based on hidden columns, as you will see.

SamT
01-30-2016, 01:35 PM
Even though you are not planning on using a database, I would still layout the workbook as if it were a database. Even if a database approach does give you way more abilities than you will ever need, it is still the easiest format to work with, even for simple things.

Then I would use VBA UserForms for data entry and VBA Code for data manipulation (reading the tables and filling in the display forms.) Using formulas will quickly make the project too hard to maintain and modify, too slow, and will really increase the file size over the same capabilities with code.

The Tables are simple to layout, see 15322, sheets Schedule_Template and Roster for suggestions in re the only tables I foresee you needing.

The Worksheet Reports I foresee are:

A Personal Schedule form to be filled out and printed via VBA Code, that has an officer's name schedule and any other information he may find useful, This would be normally hidden, since the Code is the only entity to ever use it.

One or more Main Schedules, (One for all or one per Platoon,) that only shows when the Officers are due to work, (Each officer's copy of the Schedule_Template sheet has all the Accounting info,) that is scrollable past 28 days so the schedule can be viewed as far in past or the future as scheduling is finished.

I don't know enough to say whether a Worksheet Form or a UserForm would be better for actually inputting schedules. In either case, all constraints must be built into the Code that manages this form.

stltom554
01-30-2016, 01:51 PM
I sure like where you are going with this, but with my skill set (or lack thereof) it will take me a long time to build that. I have already run into problems, as you pointed out, with changes, updates, etc. with the file. And given there are four different files (A PLT, B PLT, etc) it becomes a huge undertaking. That also doesn't include the fact I have to develop something for the dispatchers and administration folks who work on a 40 hour/week schedule with different start dates. This was phase 1. Take care of patrol and then start working my way up. I can create the database portion, but I have no idea how to code all of this. I also have to keep it in this format.

SamT
01-30-2016, 04:36 PM
All coding is based on

If this then do that
Else do other.

As an example for planning your code

For each Officer in Roster
For each date
If Code = 101, 102, 103, 104 ,120, etc then
On Master Schedule place X in Date next to Officer

Those are called Rules, and we can help you once we know the Rules.

Then there are Constraints. Examples of Constraints are

No officer can work more then 120 hr is two weeks
No officer can work more than 16 hours in a row
No patrol shift can have more than 8 officers.
No shift can have more than one ride-along
The appearance of the Final Schedule is a Constraint, that we have to work with.

You should know that I am mostly a VBA coder, not a formula man. It is possible that one of our true Excel Experts can see a way to easily produce Schedules, (Tables or Pivot Tables or ??,) from the Database Tables I suggested. :dunno Wait a couple of days and see if they chime in.

In the meantime, seeing the Rules and Constraints will help them understand exactly what you need. Rules and Constraints are a way for non-programmers to talk to programmers (because we don't speak the same language.) Axually, we say that we don't know the Client's Domain. It sounds better that way. :D

As to data entry, I suggest that we start with a simple Worksheet Form that holds 28 days for one platoon and simple Command Button to transfer the data to the individual Officer Schedules.This is fairly easy coding.

The Rules would be:
For Each Officer on Data Entry Form
For each Date
If Date has Code then
Add Code to Officer Schedule Sheet in Code column next to Date

This gets the project in production quickly.

As the concept is proven and the code is Perfected, we can add Features like:
A Spinnable Schedule Start Date box = Easy
A Platoon dropdown and a dependent Roster dropdown= Easy.
Drop downs for Codes= Easy
Pop Ups to warn when Constraints are violated.= Easy
Auto-filling any pre-scheduled slots

In my imagination I see this working like:
Spin the Schedule Start Date, Select a Platoon, Double click an Officer slot and select a name. Double click a date Cell and select a Code.

Repeat for next Date Cell, Officer, Platoon, Schedule Period

stltom554
01-30-2016, 11:52 PM
Great information. Thank you so much. Let me see if I can help you.

Each platoon will have its own file. Within that file I need 14 schedules. The first schedule has the first start date and each of the 13 schedules after that will automatically be populated with the dates. This allows the Sgt/Cpl to enter vacation requests, training dates, etc. up to a year in advance.

The codes you see (101, 102,103,120 and ROV etc) are sector assignments (all equalling 12 hour work days). The other items you see in the values list are various codes for various functions:

Code
4/C = 4 hour duty day but using 4 hours of Comp instead of working
4/V = 4 hour duty day but using 4 hours of Vacation instead of working
4/X = 4 hour duty day. The X stands for Rec time (hours off/days off)
8/C = 8 hour duty day but using 4 hours of Comp instead of working
8/V = 8 hour duty day but using 4 hours of Vacation instead of working
8/X = 8 hour duty day
[C is an entry where I need to add a value. For instance if C is entered I need to enter how many C hours)]
C = Comp time
F/T = Field Training. The hours will match the FTO on the same schedule. So if someone is in F/T with C4, those hours will match. So ultimately I would like to enter F/T and have it ask "Who is the FTO for this date?" Then select the FTO from a list (low priority function)
H = Holiday (not used currently. Will eventually match a duty day (8 or 12 hours)
L/D (12) = Light duty - 12 hours
L/D (4) = Light duty - 4 hours
L/D (8) = Light duty - 8 hours
M/L = Military Leave (12 hours)
M/L (4) = Military Leave (4 hours)
M/L (8) = Military Leave (8 hours)
M/L/C = Military Leave (12 hours COMP) This occurs when military leave time has been depleted
M/L/C (4)= Military Leave (4 hours COMP) This occurs when military leave time has been depleted
M/L/C (8)= Military Leave (8 hours COMP) This occurs when military leave time has been depleted
M/L/V = Military Leave (12 hours VAC) This occurs when military leave time has been depleted
M/L/V (4)= Military Leave (4 hours VAC) This occurs when military leave time has been depleted
M/L/V (8)= Military Leave (8 hours VAC) This occurs when military leave time has been depleted
O = Other (12 hours)
O (4.00) = Other (12 hours)
O (8.00) = Other (12 hours)
ROV = Rove unit (12 hours)
[S/L is an example of wanting to enter "S/L" and being prompted for x amount of hours]
S/L = Sick Leave (12 hours)
S/L (4) = Sick Leave (4 hours)
S/L (8) = Sick Leave (8 hours
S/T = School/Training (12 hours)
S/T (4) = School/Training (4 hours)
S/T (8) = School/Training (8 hours)
S/T/C (4)= School/Training (4 hours worked, 8 hours COMP)
S/T/C (8)= School/Training (8 hours worked, 4 hours COMP)
S/T/V (4)= School/Training (4 hours worked, 8 hours VAC)
S/T/V (8)= School/Training (8 hours worked, 4 hours VAC)
TEST
V = VAC (12 hours)
V (4)/X = Taking 4 hours vacation in conjunction with a 8 hours REC
V (8)/X = Taking 8 hours vacation in conjunction with a 4 hours REC
X = REC day. No value

The only major constraint is that officer's are not supposed to exceed 160 hours, however, sometimes it happens based on manpower issues, emergency call outs, etc.

Another constraint is that we have to have a minimum manpower of 5. Anything less than that will be flagged (probably conditional formatting.

I hope this helps. I have eventual goals of tying other departments into these schedules. For instance, admin has to maintain a dry erase board of who is working and who is not. I was going to link a daily schedule file to all four of the platoon schedules to show them who is working on one page that can be printed.

This really needs to be a hosted web interface with a SQL table driving it. I just don't have the skill set to build it. And to get the command staff to come off of Word schedules to uniform the document and add some automation was a monumental task to put it lightly.

stltom554
01-30-2016, 11:54 PM
Oh. And the officers on the platoons are generally static. We very rarely change folks around.

SamT
01-31-2016, 10:01 AM
This really needs to be a hosted web interface with a SQL table driving it
Why put it on the internet? Y'all have a network. And unless your 15 year plan predicts an amazing population growth, Excel is more than enough for your data. WAMP will put a web server on any Windows computer, I have it on mine to host my personal Baen (http://www.baenebooks.com/c-1-free-library.aspx) html library. Excel has built-in web functions and the actual html pages you need are static until the Schedule changes.

WAMP Links:
https://en.wikipedia.org/wiki/LAMP_%28software_bundle%29#WAMP
http://wamp-proto.org/
http://sourceforge.net/projects/wampserver/

IMO, 99.9% of LEOs in the nation have the expertise to install and administrate WAMP. As long as they are willing to :rtfm:

WAMP has SQL , but I expect that you would need someone to write some server side scripts to use it like you need, :dunno. Excel can certainly interface with it or it can write directly to the static html stored on the server. Those are outside my expertise, but others here can do it.

IMHO, the differences between using WAMP's SQL and Excel for the Data Base:
WAMP: many can modify the table at once.
Excel: only one person at a time can modify it.

WAMP:, you also need someone to Program and maintain the SQL Server for use as a standard Database.
Excel: VBA uses Basic English for Key Words, most are intuitive to English speaking Excel users.

If you already have a network server, it can host WAMP and the Excel Schedule Workbook allowing all Schedulers easy access to the Workbook.

An Excel Worksheet can store billions of Dates on one sheet, but I recommend that each year it be purged of the previous year's schedule.

Here's a new Programmers term for you: Scenario. A Scenario is an example of how the Project will be used

Example Scenarios:


John wants to see and/or print his schedule for the Easter weekend
The Chief wants to see the department schedule for Spring Break.
The Sergeant needs to put John on Vacation on July 3 - 10
Need to backdate John's Record to change from no-show yesterday to emergency vacation and extend the Scheduled vacation until next week.
Need to update the Record, but not the schedule, to show the work hours for that major incident last night.
Need to daily up date the records to show who didn't show, was late, or took off early.

stltom554
02-03-2016, 05:45 PM
Thank you for the information sir. I will be researching. I may have the skill set to maintain this. Server side scripting may be a challenge but I'll see.

Thank you so much for the time and detailed response!!