texashuntingforum.com logo
Main Menu
Advertisement
Affiliates
Advertisement
Newest Members
Sakohunter264, Pups4Sale, Sevensalmon, FacePaint, GavinBryant
70595 Registered Users
Top Posters(All Time)
dogcatcher 110,646
bill oxner 91,416
SnakeWrangler 64,162
stxranchman 60,296
Gravytrain 46,950
RKHarm24 44,585
rifleman 44,461
BMD 41,241
Forum Statistics
Forums46
Topics616,488
Posts11,416,361
Members85,595
Most Online19,184
Feb 5th, 2020
Print Thread
Page 1 of 2 1 2
EXCEL help!!! #8726110 11/04/22 05:55 AM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
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?


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: BigPig] #8726115 11/04/22 08:45 AM
Joined: Apr 2016
Posts: 27
J
JW_TX Offline
Light Foot
Offline
Light Foot
J
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,365
P_102 Online Content
Veteran Tracker
Online Content
Veteran Tracker
Joined: Sep 2008
Posts: 3,365
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: 8,112
N
ntxtrapper Online Content
THF Trophy Hunter
Online Content
THF Trophy Hunter
N
Joined: Mar 2010
Posts: 8,112
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.


"Whosoever is delighted in solitude, is either a wild beast or a god."

- Aristotle
Re: EXCEL help!!! [Re: ntxtrapper] #8726122 11/04/22 10:10 AM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by ntxtrapper
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.


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: P_102] #8726123 11/04/22 10:13 AM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by JW_TX
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…

Originally Posted by P_102
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.


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: BigPig] #8726129 11/04/22 10:48 AM
Joined: Dec 2008
Posts: 38,747
J.G. Online Content
THF Celebrity
Online Content
THF Celebrity
Joined: Dec 2008
Posts: 38,747
Hit it with a hammer.


[Linked Image]
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: 33,979
Guy Offline
THF Celebrity
Offline
THF Celebrity
Joined: Dec 2005
Posts: 33,979
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: 33,979
Guy Offline
THF Celebrity
Offline
THF Celebrity
Joined: Dec 2005
Posts: 33,979
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: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by J.G.
Hit it with a hammer.


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


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: Guy] #8726258 11/04/22 01:58 PM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by Guy
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?


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: BigPig] #8726308 11/04/22 02:54 PM
Joined: May 2009
Posts: 23,210
D
dkershen Offline
Rev Dave
Offline
Rev Dave
D
Joined: May 2009
Posts: 23,210
Originally Posted by BigPig
Originally Posted by JW_TX
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…

Originally Posted by P_102
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 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


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: 8,586
The Dude Abides Offline
THF Trophy Hunter
Offline
THF Trophy Hunter
Joined: Dec 2009
Posts: 8,586
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.

Re: EXCEL help!!! [Re: BigPig] #8726331 11/04/22 03:04 PM
Joined: Nov 2004
Posts: 14,653
jeh7mmmag Offline
gramps
Offline
gramps
Joined: Nov 2004
Posts: 14,653


�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: 33,979
Guy Offline
THF Celebrity
Offline
THF Celebrity
Joined: Dec 2005
Posts: 33,979
Originally Posted by BigPig
Originally Posted by Guy
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"

[Linked Image]

Re: EXCEL help!!! [Re: Guy] #8726419 11/04/22 05:34 PM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by Guy
Originally Posted by BigPig
Originally Posted by Guy
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"

[Linked Image]


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.


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: BigPig] #8726469 11/04/22 06:52 PM
Joined: Aug 2009
Posts: 4,258
D
decook Online Content
Extreme Tracker
Online Content
Extreme Tracker
D
Joined: Aug 2009
Posts: 4,258
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]


Press [Linked Image] for an AMERICAN.
Re: EXCEL help!!! [Re: BigPig] #8726495 11/04/22 07:28 PM
Joined: Jan 2012
Posts: 27,709
T
TXHOGSLAYER Online Content
THF Celebrity
Online Content
THF Celebrity
T
Joined: Jan 2012
Posts: 27,709
Pivot table?


5303

LETS GO BRANDON
Re: EXCEL help!!! [Re: decook] #8726500 11/04/22 07:31 PM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by decook
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]



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

[Linked Image]


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: TXHOGSLAYER] #8726503 11/04/22 07:34 PM
Joined: Aug 2009
Posts: 4,258
D
decook Online Content
Extreme Tracker
Online Content
Extreme Tracker
D
Joined: Aug 2009
Posts: 4,258
Originally Posted by TXHOGSLAYER
Pivot table?

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] for an AMERICAN.
Re: EXCEL help!!! [Re: BigPig] #8726515 11/04/22 07:53 PM
Joined: Aug 2009
Posts: 4,258
D
decook Online Content
Extreme Tracker
Online Content
Extreme Tracker
D
Joined: Aug 2009
Posts: 4,258
Originally Posted by BigPig
Originally Posted by decook
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]



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

[Linked Image]




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] for an AMERICAN.
Re: EXCEL help!!! [Re: BigPig] #8726535 11/04/22 08:23 PM
Joined: Dec 2005
Posts: 33,979
Guy Offline
THF Celebrity
Offline
THF Celebrity
Joined: Dec 2005
Posts: 33,979
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: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
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


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Re: EXCEL help!!! [Re: decook] #8726636 11/04/22 10:54 PM
Joined: Feb 2012
Posts: 8,662
M
Mickey Moose Offline
THF Trophy Hunter
Offline
THF Trophy Hunter
M
Joined: Feb 2012
Posts: 8,662
Originally Posted by decook
...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.


You only think antivirus keeps you safe
Re: EXCEL help!!! [Re: Mickey Moose] #8726723 11/05/22 12:54 AM
Joined: Aug 2009
Posts: 21,129
B
BigPig Offline OP
THF Celebrity
OP Offline
THF Celebrity
B
Joined: Aug 2009
Posts: 21,129
Originally Posted by Mickey Moose
Originally Posted by decook
...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


Wade Dews, REALTOR ®
Rendon Realty, LLC
Frontline Real Estate Team
www.RendonRealty.com
WadeDews@gmail.com
214-356-2410
VETERAN & LAW ENFORCEMENT OWNED & OPERATED
$1500 back at closing for First Responders, Veterans, and Teachers.
Page 1 of 2 1 2
Previous Thread
Index
Next Thread

© 2004-2022 OUTDOOR SITES NETWORK all rights reserved USA and Worldwide
Powered by UBB.threads™ PHP Forum Software 7.7.3