Hello! Please help me make my workbook better

[eqeafarms]

Hi, I'm still very much a noob, but I've learned a lot since my first post. Mostly, how to install xl2bb so I can post my dang workbook on here.

I am tracking environmental samples that come back out of spec for my job, and I need to know how to make my workbook do a few things. I have to track any OOS results through a process involving corrective actions and retests with confirmation tests to officially close any case out. I have a ton of open investigations and I'm getting overwhelmed bc I feel like my workbook is doing too much or too little, but it's not quite where I need it and I'm not super versed with Excel so I wanted to see if anyone might have a second to just help me understand if I have created something decent so far.

Mainly, for some of these investigations, they go on for multiple rows. How can I make the workbook hide previous rows associated with the investigation, and only show the most recent active row?

Additionally, are my rules/functions stupid? Like, am I doing an okay job/am I on the right track for what I am trying to do? (sorry and thank you so much)

emp_exception_log_v2.2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
18	12-Apr	yes	eq	4/2-4/4		lpack	line 2	proseal film bar	200	4/23/2024		no	200	revision needed1				5/9/2024		irtyes		irtconfirmation pending		5/15/2024		no	continued on next row	View on Next Row
19	20-May	yes	eq	5/15/2024		lpack	line 2	proseal film bar	20					tbd								tbd						n/a
20	26-Jan	no	eq	12/2/2023		lpack	mix	belt #6 ss guard	10	1/29/2024		yes		closed														Complete
21	26-Jan	no	eq	12/2/2023		lpack	mix	incline #1	4200	2/6/2024		yes		closed														Complete
22	26-Jan	no	eq	12/2/2023		lpack	mix	incline #4	30000	2/6/2024		yes		closed														Complete
23	26-Jan	no	eq	12/5/2023		lpack	post-mix	incline #2	20	2/6/2024		yes		closed														Complete
24	26-Jan	no	eq	12/5/2023		lpack	post-mix	incline #2 side flap	10	2/6/2024		yes		closed														Complete
25	26-Jan	no	eq	12/5/2023		lpack	post-mix	incline #1 ss guard	30	2/8/2024		yes		closed														Complete
26	26-Jan	no	eq	12/5/2023		lpack	post-mix	incline #2 ss guard	10	2/8/2024		yes		closed														Complete
27	26-Jan	no	eq	12/16/2023		lpack	post-mix	upper shaker forks	10	2/8/2024		yes		closed														Complete
28	26-Jan	no	eq	12/16/2023		lpack	mix	hopper #1	290	2/8/2024		yes		closed														Complete
29	26-Jan	no	eq	12/16/2023		lpack	mix	incline #2 ss guard	7800	2/8/2024		yes		closed														Complete
30	26-Jan	no	eq	12/16/2023		lpack	mix	outfeed shaker #2	20	2/13/2024		no	5400	revision needed1	I personally attribute fluxuations in the scores for these shakers to the riveted surface of the metal, just more areas for the scrubbers to miss if someone is newer or maybe not used to cleaning a certain piece of equipment. Currently working with Bob to see what we can do to achieve better and more consistent results.	Bob agreed the issue could be solved by foaming more thoroughly and letting it sit a bit longer than previously before scrubbing.		2/26/2024		yes		confirmation pending		4/2-4/4		no	continued on next row	View on Next Row
31	12-Apr	yes	eq	4/5/2024		lpack	mix	outfeed shaker #2	10	4/21/2024		irtyes		confirmation pending				5/9-5/11		no		revision needed2		5/21/2024		yes	confirmation pending	Pending
32	27-May	yes	eq	5/21/2024		lpack	mix	outfeed shaker #2						tbd								tbd						n/a
33	26-Jan	no	eq	12/16/2023		lpack	mix	outfeed shaker #3	30	2/13/2024		no	3800	revision needed1	I personally attribute fluxuations in the scores for these shakers to the riveted surface of the metal, just more areas for the scrubbers to miss if someone is newer or maybe not used to cleaning a certain piece of equipment. Currently working with Bob to see what we can do to achieve better and more consistent results.	Bob agreed the issue could be solved by foaming more thoroughly and letting it sit a bit longer than previously before scrubbing.		2/26/2024		no	30	revision needed2		4/2/2024		no	continued on next row	View on Next Row
34	12-Apr	yes	eq	4/5/2024		lpack	mix	outfeed shaker #3	30	4/21/2024		irtyes		confirmation pending				5/9-5/11		no		revision needed2		5/21/2024		no	continued on next row	View on Next Row
35	27-May	yes	eq	5/21/2024		lpack	mix	outfeed shaker #4	100					tbd								tbd						n/a
36	26-Jan	no	eq	12/16/2023		lpack	mix	outfeed shaker #4	10	2/13/2024		no	20	revision needed1	I personally attribute fluxuations in the scores for these shakers to the riveted surface of the metal, just more areas for the scrubbers to miss if someone is newer or maybe not used to cleaning a certain piece of equipment. Currently working with Bob to see what we can do to achieve better and more consistent results.	Bob agreed the issue could be solved by foaming more thoroughly and letting it sit a bit longer than previously before scrubbing.		2/26/2024		yes		confirmation pending	this site was not confirmed with 3 swabs before it came up again during regular rotation and ultimately was out of spec, so I am including that as a third retest and going from there since this case was not technically closed	3/28/2024		no	continued on next row	View on Next Row
37	16-Apr	yes	eq	3/28/2024		lpack	mix	outfeed shaker #4	90	4/21/2024		irtyes		confirmation pending				5/9-5/11		no	320	revision needed2		5/21/2024		yes	confirmation pending	Pending
38	26-Jan	no	eq	12/16/2023		lpack	mix	outfeed shaker #5	10	2/13/2024		yes		closed														Complete
39	26-Jan	no	eq	12/29/2023		lpack	sormac	lower belt ss guard	180000	2/13/2024		yes		closed														Complete
40	26-Jan	no	eq	12/5/2023		bharvest		exterior WIP tote	10	2/15/2024		yes		closed														Complete
41	26-Jan	no	eq	12/29/2023		lpack	tomra	infeed/dump belt	>570000	2/15/2024		no	750	revision needed1	This belt is netted, not a solid belt, however where the roller wheels touch the belt underneath, water will collec and sometimes take longer to dry. I think perhaps it is this water leftover from rinsing creating the high counts here. Edit- 4/1/24: during pre-op this morning it was discovered that condensation builds up above this belt and is very likely dripping onto it after it's been cleaned, which I am almost positive is the source of the high counts.	I am working with Bob and Joshua today to see what we can do. Lalaine is going to find us an appropriate tool to use to get the condensation off the ceiling, and I suggested we cover the belts that are at risk for drips with plastic.	tomra infeed pic	2/26/2024		no	610	revision needed2		4/4/2024		no	continued on next row	View on Next Row
42	12-Apr	yes	eq	4/5/2024		lpack	tomra	infeed/dump belt	1600	4/19/2024		irtyes		confirmation pending				4/26-4/28		no		revision needed2		5/9/2024		no	continued on next row	View on Next Row
43	14-May	yes	eq	5/10/2024		lpack	tomra	infeed/dump belt	26000	5/15/2024		no	870	revision needed1		The infeed belt is in an area that gets a little warmer (while still being within range) than the rest of the room. Condensation has been seen accumulating above the belt on the ceiling and we addressed that, however nothing the team is doing seems to be working consistently to get this belt cleaned to below 10cfu/sponge.		5/21/2024		no	2900	revision needed2						n/a
44	26-Jan	no	eq	12/29/2023		lpack	tomra	middle belt side flap	>570000	2/15/2024		yes		closed														Complete
45	26-Jan	no	eq	12/29/2023		lpack	tomra	outfeed belt	>570000	2/15/2024		yes		closed														Complete
zone1 oos log

Cell Formulas
Range	Formula
AA18:AA45	AA18	=IF(Z18="no","continued on next row",IF(Z18="yes","confirmation pending",IF(Z18=""," ",IF(Z18="3swabsyes","closed"))))
AB18:AB45	AB18	=IF(N18="closed","Complete",IF(V18="closed","Complete",IF(AA18="continued on next row","View on Next Row ",IF(AA18="confirmation pending","Pending",IF(AA18=" ","n/a",IF(AA18="closed","Complete"))))))
N18:N45	N18	=IF(A18="","",IF(L18="yes","closed",IF(L18="no","revision needed1",IF(L18="","tbd",IF(L18="irtyes","confirmation pending")))))
V18:V45	V18	=IF(A18="","",IF(N18="closed","",IF(T18="yes","confirmation pending",IF(T18="no","revision needed2",IF(T18="","tbd",IF(T18="irtyes","irtconfirmation pending"))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AB:AB	Cell Value	contains "Complete"	text	NO
V:V	Cell Value	contains "confirm"	text	NO
N6:N1048576,AA:AB	Cell Value	contains "closed"	text	NO
AA6:AB1048576	Cell Value	contains "confirm"	text	NO
AA6:AB1048576	Cell Value	contains "continue"	text	NO
N6:N1048576,V6:V1048576	Cell Value	contains "confirmation pending"	text	NO
N6:N1048576,V6:V1048576	Cell Value	contains "tbd"	text	NO
N6:N1048576,V6:V1048576	Cell Value	contains "revision"	text	NO
B6:B1048576	Cell Value	contains "no"	text	NO
B6:B1048576	Cell Value	contains "yes"	text	NO
T6:T1048576,Z6:Z1048576,L6:L1048576	Cell Value	contains "no"	text	NO
T6:T1048576,Z6:Z1048576,L6:L1048576	Cell Value	contains "yes"	text	NO

 

[Micron]

I was an ISO 14001 environmental coordinator and compliance auditor for a time, so I don't say this lightly.
Your best bet would be to ditch Excel and do this in Access. Your hardest task would be to do this in Access (assuming you are not well versed in it). It has a large learning curve if you want to do it really well.

How can I make the workbook hide previous rows associated with the investigation, and only show the most recent active row?

This would be a no-brainer in Access by using queries. It would also beat Excel in terms of inputting test data and reporting on results, both current and historical. Having said that, perhaps Power Query would help you with that, but I know nothing about it.

 

[eqeafarms]

I was an ISO 14001 environmental coordinator and compliance auditor for a time, so I don't say this lightly.
Your best bet would be to ditch Excel and do this in Access. Your hardest task would be to do this in Access (assuming you are not well versed in it). It has a large learning curve if you want to do it really well.

This would be a no-brainer in Access by using queries. It would also beat Excel in terms of inputting test data and reporting on results, both current and historical. Having said that, perhaps Power Query would help you with that, but I know nothing about it.

Thank you I will check it out. I appreciate you taking the time to respond, have a nice day!

 

[Micron]

Post 4 here might interest you

Best approach for multiple look ups?

So, I want create a form that will utilise multiple datasets and am looking for some advice on the best way to do this. In as basic an explanation as possible… Step 1: I enter a postcode Step 2: It automatically looks up the LSOA from a very large dataset (too large for one sheet, spreads...

www.mrexcel.com