Forum Discussion
Creating a multi location stratagy
Oh, my. As the saying goes, "No good deed goes unpunished."
I have no specific "best practices" to recommend; just a little experience with the consequences of designing some elaborate spreadsheets that I know how to manage, but can be very difficult to pass on to others.
One personal practice--whether or not it's "best practice" I leave to others--is that I generally avoided VBA and macros, preferring to let Excel's many built-in functions do the work. [Often doing so faster than a macro could.]
Another general recommendation--and I suspect you'll hear this from others--is that whenever possible keep database design on the input end of things simple, using Excel Tables as much as possible, and then using Pivot Tables or Power Query to produce the output / reports. My guess is that you're already doing that--but that would mean (for example) one sheet that contains the data on all 9 vehicles; one sheet that contains the data on all 90 members; and so forth. You mention seven different sheets, one for each of the days of the week: is that necessary at the input end of things?
You haven't actually described what the ultimate business purpose(s) is (are)....I assume such things as scheduling the 90 members in some kind of optimum way, making sure the vehicles are maintained on a regular basis, etc. Do you actually have ONE workbook that manages everything on multiple sheets? It sounds like (if that is the case) they could be broken into a few, just so different people cold be responsible for different aspects of the overall project.
Anyway, congratulations: let's see what comes forth from others..... you're to be commended for what you've done so far. Has this been a labor of love? (i.e., are you an amateur --- meaning someone who does this from sheer love of the task ---- or have you been trained in data management?)
(just curious; I'm self-taught when it comes to Excel, and just have fun working with it effectively; still have a lot to learn)
So, first let me say that this is where you pause to refill your coffee... I wasn't gonna go to far into background, but you asked so here we go.
First some perspective:
The VERY unofficial slogan of the Fire Dept. of New York - "150 years of tradition unencumbered by progress". Now obviously this is facetious.... but....
In the Bureau of Emergency Medical Services, we still have a lot of that sentiment on the admin side. I should say that the nuts & bolts medicine we practice is fairly cutting edge considering this size of our agency. In the City of New York, we average around 4500 jobs a day. That's what many smaller municipalities do in a year.
About me:
I am an FDNY Paramedic who was promoted to the rank of EMS Lieutenant in 2010. (so some time under my belt) Aside from my operational and medical duties, I also have significant administrative responsibilities as well. One of those is this, Daily Staffing.
My computing philosophy - "If the computer doesn't make it easier, use a pencil."
My first PC - 8088 processor, 256k ram, 10mb hard drive, DOS 2.10
You asked if I am an Excel pro or amateur - I am a professional Paramedic and an amateur everything else. Excel for me is a tool like my wood planes. I use them well, but some guys can smooth a table top in a way that I can only marvel at and appreciate for its inherent beauty.
The Task:
For *MANY YEARS* the daily rundown has been written by hand on a form in pencil then faxed to the division office. Each station used a form of its own design. The administrative EMT's at the Division Office would then collate all that info on to another form (in pencil) and forward it to the Fire Dept Operations Center (FDOC) where info from the 5 EMS divisions would be further collated onto one single master form. This happens 3 times a day. I don't have to describe all the inefficiencies here.
To her credit, our current Division Commander wants to get all stations on to a single unified form, typed.
The Problem:
This Division Commander is so far removed from the job that my peers and I do. She gave only the vaguest instructions to a Captain at the division office who then delegated it to a young EMT. That EMT then dropped lines, boxes and some horrific formatting onto an excel sheet, called it The New Form, and sent it to two station (not mine) to "pilot test". I heard about it and got a copy to see for myself.
You asked if this was a labor of love. No, it was a labor of fear. I immediately recognized that this new sheet was about to increase our workload, complicate data collection, create resentment and fix nothing. I knew the basic intent was sound and I had been thinking about it for years. I was forced into action. What I created was a tool for Lieutenants, designed by Lieutenants, that made the job easier, faster and more accurate. Fear is a powerful motivator.
So here we are. The tool is more sophisticated than the division staff could have produced. The administrative EMT's at the division like the form that comes from our station best. It's actually designed for their eyes to easily track over the data. (I worked on that with them) They are the ones who lobbied for this project to be expanded division wide. That brings us to now. So many more features can be built in to this project, and people are asking.
What I need is a strategy to be able to update code without disturbing each station's unique data set.
I'm expecting people to tell me that I have to break up the project in to two workbooks: one with data, one with programming. If that's the case, I'm interested in any pitfalls to watch for or tricks to make it easier.
That said, I wish there were some ultra-cool way to avoid the split altogether.
Sorry for the long-windedness,
Brett
Brett--I smiled all the way through that. And as a NYC resident, I thank you for your service, your dedication. I'm up in Washington Heights, the far end of Manhattan, so our paths aren't likely to cross except on purpose, but if you ever venture up to these parts, drop a line through the personal messages here---I'd like to meet you.
The VERY unofficial slogan of the Fire Dept. of New York - "150 years of tradition unencumbered by progress".
During my working career (retired since 2002) I was with a large corporation headquartered on Manhattan (but with Brooklyn roots); at one point we were called on by the IT department(s) of NYC to see if we could offer some wisdom in getting control of all their systems. What you write tells me that the problems persist. And not surprising. Just like all the other infrastructure of a major metropolitan area--things are working, kinda, and the rapid pace of life/traffic/life&death makes it next to impossible to update and improve those existing situations. You've got your 4300 EMS calls to make every day; no time to fix the daily rundown.
Well, Brett, I hope somebody comes along in this forum to help you (to say nothing of NYC as a whole). This sounds like a project some corporation should take on as a public service.
There actually are several programs that do this. The Department has been looking into these products, but the municipal procurement process comes with its own.... uh... eccentricities?
In the mean time, EMS Lieutenants across the city still have a job to do. We have historically had to do more with less, and so it goes.
One day, the powers that be will settle on a solution and my program will head to the trash. But until then... 😉
B.
