EXCEL help!!!

I’m still working on this EXCEL spreadsheet. I’m completely stuck with what I need and nobody has been able to help me, and YouTube university isn’t showing me what I need.

I have numerous drop down boxes with officers names. There’s 7 different lists they can fall under depending on their schedule that day and they can only be on 1 list at a time. This varies from day to day and I’m not the only person that will be using this system, so I need to limit the possibility of errors as much as possible.

So if I move an officer from say “On-Duty” to a list called “Other Days Off” I would like for it to automatically delete them from the “On-Duty” list. Again, they can only be on the detail in 1 spot per day for reporting reasons.

Clear as mud?

What you’re talking about is likely going to require a custom macro.

An alternative could be a series of validation formulas to count how many times a name shows up per day. If the count is more than one, you have a problem…

Agree on the macro, they can be long and painful initially but are great with the end result.

You must be the admin guy. I would usually just type control and f, bring up the search function and look for their ID number to make sure I didn’t have an officer on more than one page.

Yeah I’ve been in this position about 2 years. I enjoyed the streets for 15 years but I no longer trust our DA, especially after the 2020 riots, so I took a desk job until things hopefully change.

Where would I find information on this? I’m okay with it taking a while, this system will be in use for decades. Our old system is DOS, and it’s fantastic except for 2 things: 1) it now longer talks to our printer because they all work on windows 10 and the computer that supports DOS is like a Windows 5 or 7; 2) the man that wrote the DOS program in 1990 is getting very old and he’s the only one with the ability to fix it when an error occurs.

Hit it with a hammer.

Why not “cut” name from one list and “paste” into the other list? This accomplishes what you asked for.

I would replace the 7 lists with one table with filters. Column A is the name, column B is your 7 list names, then set up filters on the table, you can filter on column B, pick “on duty” from the drop down filter menu, and you got your list of On Duty names.. same for the 6 categories.

Google “excel table with filters”, or youtube..

I would not mess with macros, that is making it way more complicated than need be. There is a simple solution to what you want to accomplish.

That almost occurred last night when the “new” computer wouldn’t run 2 different Excel spreadsheets at the same time

Would that essentially be just 2 columns?

DOS? On a Windows 5 or 7 machine? That's scary as hell. In reality a spreadsheet is not the best tool for the job. Something that uses a database and then can export excel reports would be much better. There's a [censored] ton of inexpensive scheduling apps out there. Just my

I'm actually surprised DPD didn't sub this out to a third party that specializes in custom software apps to develop and integrate. Doing this in Excel is as antiquated as DOS is.

Create your record then
Look into Access VBA

https://support.microsoft.com/en-us...ing-92eb616b-3204-4121-9277-70649e33be4f

Have fun

Yes. One column is name, the other column work status. Simple example below. First screenshot is unfiltered table with 4 people. Second screenshot I filtered on "On Duty"

We had one like that, we refused to use it after the city spent millions on it. Problem is, we need to be able to quickly see who is working and place partners next to them. The city didn’t listen to us and instead bought a system designed for the FD.

Yuo could do a list with the 7 options, like I did in this screenshot. You could even make a vlookup table to be extra fancy and make the changes happen on a different sheet when you choose a different status. I totally agree that excel isn't the right tool for the job, especially when you say many others will use it too. Excel doesn't lock records but does lock the file open, so you'll have open file issues when trying to save. A database is what you want.

Learn access, it's not too hard and will do what you want. Just remember one thing regarding security - they don't call it "access" for nothing. Don't keep PI in it.

Pivot table?

I may give access a try next week. This is the layout I need.

It could be used, but BP's use case has separate lists for the officers. That's why I recommended a vlookup to handle this. The officers would only show up in the column they were "assigned" to, depending on the status.

Yeah that's not too complex. It could be done. Still, you're better off putting your time to a database. Use what you have by copy paste for the short term until you get what you want in access. Trust me, anyone that does what you do AND runs real estate at the same time is not going to be too challenged by access. You'll wind up having fun along learning it too.

What you want to do is set up a master table, like what I posted before, first column is the name, then another 7 columns for each day of the week. This will be the table you maintain/update. And if you want to, you could do drop downs like decook posted.

Then, all your lists have "VLOOKUP" formulas that point to the master table, it looks up the name, and the column reference is the day of week, and it pulls in that status. So with this formula, each list will use the same formula pull in the status of each employee. At this point, all the lists will look the same, this is step 1 get this working. Then add an "if" formula in each cell/each list. For "Regular Days off", your formula says, for each name in the list, if value = "Regular Days off", then show employee name, otherwise blank. And do this for each list.

With this, you maintain the master table, and all the lists get automatically updated, for each day of the week. If I get some time later I'll post an example.

Anything outside of the normal Microsoft Office products has to be installed by IT, it also has to go through a memo to the chief then to the COD for approval, then put out for lowest bid. So I’m limited in what I can use.

Believe it or not, the DOS system is perfect and very easy and fool proof. It eliminates any possible errors that can easily occur in Excel

While Excel almost certainly wouldn't be my first choice, it's not necessarily a bad choice. Regardless, BigPig clarified some points as to why he had the idea (IT, memo to Chief, etc), but there's a big point which hasn't been addressed. Are the multiple users simultaneous or not. That makes a big difference. Excel can deal with multiple users (file locking, read-only) just not ideally. If they are simultaneous then absolutely a database-backed system (no matter what that is) is a better solution. The answer is so simple as a Javascript-driven web page (doesn't even need a web server - could be on a file share, don't need a web server since JS is processed client-side). The JS could be native or even Jquery (with Jquery UI or Bootstrap, just two examples). Then the web page, yes even without a web server, talks to a database via AJAX. There's a database server there already; just need to add the database for this specific need. There could even be logging in the database of who made what changes.

So, HTML with JS and Mysql/Postgres/MS SQL/etc. Boom, problem solved. Would take about one hour.

Da fuq u just say?

Call Kylie, he can translate that

This is like watching two monkeys fornicate a football…what’s next, you going to take up C#?

LOL.

I vote for keeping the DOS system going. I'm sure it'd be very easy.

They’re all right about using a DB or scheduling package. Yes, EXCEL will work with macros but every time a schedule changes a new macro would have to be written. Handy for lengthy, set schedules, not so much for short term ones.

C# and JavaScript with SQL back end is easy. We build all our apps on those.

This is going to be best managed manually in excel, needs to be done by an excel wiz so it’s done right, or software as a service. I’m surprised a SaaS doesnt exist for this. Is your PD doing something wildly different than any other PD in the US? Access is not the answer. Hacking at a spreadsheet and macros will mean problems down the road.

No need for SaaS because it doesn't take a wiz to do it.

Imagine how many software packages and SaaS offerings wouldnt exist if everyone just shrugged their shoulders and said it didnt take a wiz to solve the problem on pen & paper, excel, access…think beyond the one PD my friend. Wanna start up a SaaS together? I know you’re an IT guy. I’m a Sales guy. We probably already have 1 client. Lets do this. Although a quick google search makes it seem we’ve missed the boat and the OPs problem has seemingly been solved many times over. No problem. We can pivot to PD SaaS consulting + resellers + implementation.