Forums46
Topics537,927
Posts9,730,766
Members87,051
|
Most Online25,604 Feb 12th, 2024
|
|
|
EXCEL help!!!
#8726110
11/04/22 05:55 AM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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?
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726115
11/04/22 08:45 AM
|
Joined: Apr 2016
Posts: 27
JW_TX
Light Foot
|
Light Foot
Joined: Apr 2016
Posts: 27 |
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…
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726117
11/04/22 09:39 AM
|
Joined: Sep 2008
Posts: 3,772
P_102
Extreme Tracker
|
Extreme Tracker
Joined: Sep 2008
Posts: 3,772 |
Agree on the macro, they can be long and painful initially but are great with the end result.
Do not trifle in the affairs of dragons, for thou art crunchy and taste good with ketchup.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726118
11/04/22 09:40 AM
|
Joined: Mar 2010
Posts: 12,163
ntxtrapper
THF Celebrity
|
THF Celebrity
Joined: Mar 2010
Posts: 12,163 |
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.
|
|
|
Re: EXCEL help!!!
[Re: ntxtrapper]
#8726122
11/04/22 10:10 AM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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.
|
|
|
Re: EXCEL help!!!
[Re: P_102]
#8726123
11/04/22 10:13 AM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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. 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.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726129
11/04/22 10:48 AM
|
Joined: Dec 2008
Posts: 41,171
J.G.
THF Celebrity
|
THF Celebrity
Joined: Dec 2008
Posts: 41,171 |
![[Linked Image]](http://www.precisionriflehunters.com/wp-content/uploads/2017/07/garvey.jpg) 800 Yard Steel Range Precision Rifle Instruction Memberships and Classes Available
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726224
11/04/22 01:20 PM
|
Joined: Dec 2005
Posts: 35,489
Guy
THF Celebrity
|
THF Celebrity
Joined: Dec 2005
Posts: 35,489 |
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..
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726243
11/04/22 01:45 PM
|
Joined: Dec 2005
Posts: 35,489
Guy
THF Celebrity
|
THF Celebrity
Joined: Dec 2005
Posts: 35,489 |
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.
|
|
|
Re: EXCEL help!!!
[Re: J.G.]
#8726250
11/04/22 01:50 PM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
That almost occurred last night when the “new” computer wouldn’t run 2 different Excel spreadsheets at the same time ![dead_horse dead_horse](/forum/images/graemlins/default/deadhorsebeat_4.gif)
|
|
|
Re: EXCEL help!!!
[Re: Guy]
#8726258
11/04/22 01:58 PM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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.. Would that essentially be just 2 columns?
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726308
11/04/22 02:54 PM
|
Joined: May 2009
Posts: 24,354
dkershen
Rev Dave
|
Rev Dave
Joined: May 2009
Posts: 24,354 |
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. 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. DOS? On a Windows 5 or 7 machine? That's scary as hell. ![eek2 eek2](/forum/images/graemlins/default/icon_eek.gif) 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 ![2cents 2cents](/forum/images/graemlins/default/2cents.gif)
To be sure of hitting the target, shoot first and call whatever you hit the target.www.NewHopeEquine.com - Health and Healing through Horses.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726324
11/04/22 03:02 PM
|
Joined: Dec 2009
Posts: 9,822
The Dude Abides
THF Trophy Hunter
|
THF Trophy Hunter
Joined: Dec 2009
Posts: 9,822 |
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.
I am still looking for the perfect apron, one with reinforced knee areas would be perfect.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726331
11/04/22 03:04 PM
|
Joined: Nov 2004
Posts: 14,768
jeh7mmmag
gramps
|
gramps
Joined: Nov 2004
Posts: 14,768 |
�Everybody needs beauty as well as bread, places to play in and pray in, where nature may heal and give strength to body and soul.� ~ John Muir
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726366
11/04/22 03:38 PM
|
Joined: Dec 2005
Posts: 35,489
Guy
THF Celebrity
|
THF Celebrity
Joined: Dec 2005
Posts: 35,489 |
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.. Would that essentially be just 2 columns? 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"
|
|
|
Re: EXCEL help!!!
[Re: Guy]
#8726419
11/04/22 05:34 PM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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.. Would that essentially be just 2 columns? 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.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726469
11/04/22 06:52 PM
|
Joined: Aug 2009
Posts: 4,593
decook
Extreme Tracker
|
Extreme Tracker
Joined: Aug 2009
Posts: 4,593 |
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. ![[Linked Image]](https://texashuntingforum.com/forum/pics/userpics/2022/11/full-15091-368699-excelhelp.png)
Press ![[Linked Image]](https://texashuntingforum.com/forum/pics/usergals/2017/01/full-15091-89291-full_15091_35556_phone1a.png) for an AMERICAN.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726495
11/04/22 07:28 PM
|
Joined: Jan 2012
Posts: 29,128
TXHOGSLAYER
THF Celebrity
|
THF Celebrity
Joined: Jan 2012
Posts: 29,128 |
LETS GO BRANDON
|
|
|
Re: EXCEL help!!!
[Re: decook]
#8726500
11/04/22 07:31 PM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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. I may give access a try next week. This is the layout I need. ![[Linked Image]](https://texashuntingforum.com/forum/pics/userpics/2022/11/full-15111-368707-7b333766_f6ea_447f_bfd2_c981125e5028.png)
|
|
|
Re: EXCEL help!!!
[Re: TXHOGSLAYER]
#8726503
11/04/22 07:34 PM
|
Joined: Aug 2009
Posts: 4,593
decook
Extreme Tracker
|
Extreme Tracker
Joined: Aug 2009
Posts: 4,593 |
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.
Last edited by decook; 11/04/22 07:49 PM.
Press ![[Linked Image]](https://texashuntingforum.com/forum/pics/usergals/2017/01/full-15091-89291-full_15091_35556_phone1a.png) for an AMERICAN.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726515
11/04/22 07:53 PM
|
Joined: Aug 2009
Posts: 4,593
decook
Extreme Tracker
|
Extreme Tracker
Joined: Aug 2009
Posts: 4,593 |
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. I may give access a try next week. This is the layout I need. 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.
Press ![[Linked Image]](https://texashuntingforum.com/forum/pics/usergals/2017/01/full-15091-89291-full_15091_35556_phone1a.png) for an AMERICAN.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726535
11/04/22 08:23 PM
|
Joined: Dec 2005
Posts: 35,489
Guy
THF Celebrity
|
THF Celebrity
Joined: Dec 2005
Posts: 35,489 |
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.
|
|
|
Re: EXCEL help!!!
[Re: BigPig]
#8726552
11/04/22 08:51 PM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
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
|
|
|
Re: EXCEL help!!!
[Re: decook]
#8726636
11/04/22 10:54 PM
|
Joined: Feb 2012
Posts: 9,730
Mickey Moose
THF Trophy Hunter
|
THF Trophy Hunter
Joined: Feb 2012
Posts: 9,730 |
...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. 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.
My botnet is bigger than yours.
|
|
|
Re: EXCEL help!!!
[Re: Mickey Moose]
#8726723
11/05/22 12:54 AM
|
Joined: Aug 2009
Posts: 22,716
BigPig
OP
THF Celebrity
|
OP
THF Celebrity
Joined: Aug 2009
Posts: 22,716 |
...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. 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? ![nidea nidea](/forum/images/graemlins/default/noidea.gif)
|
|
|
Moderated by bigbob_ftw, CCBIRDDOGMAN, Chickenman, Derek, DeRico, Duck_Hunter, hetman, jeh7mmmag, JustWingem, kmon11, kry226, kwrhuntinglab, Payne, pertnear, rifleman, sig226fan (Rguns.com), Superduty, TreeBass, txcornhusker
|