Newbie with “IF” problems

[Excelnewbieneedtolearn]

Alright so, I have a sheet that we use daily for production tracking in a factory and the one we’ve been using was good but it had a lot of now irrelevant information on it (company changes). Now most of the code I worked out how it functioned within the sheet and changed what I could but I’m stuck on a function I’m trying to figure out for it.

It’s got a column for “production status” (“IP”,”Ready”) that works when the rest of the sheet corresponds correctly, my problem comes when we use different materials/products between jobs. Currently it all has to say “Done” before production changes to “Ready” which is fine but can be time consuming as most of it is done on phones/tablets not on pc. Could I add a column for material type and have it ignore certain data to change production status to “Ready”?

Any help with this would be greatly appreciated

 

[coulombevin]

Hi @Excelnewbieneedtolearn,

Here's what I would do with a 365 version:

- Add a new column with required products separated by comma "," as long as the name is the same text content (not case sensitive and Trim will remove spaces before after and multi spaces)
- Add that formula in the Production Status column by changing tableOfProducts to meet your table size and requiredProducts column position (dont put $ befor the number for this one).

Finaly drag down the formula and the formula will define if all products are there to make it ready.

2024-01-22_varcall-m-pop3-parhomo_miss_chi-0.01_5-0.076_13-0.076_..._384-0.434_over-0.436 (version 1).xlsb
	K	L	M	N	O	P	Q	R	S	T	U
1	Production Status	Required products	Walls	Main Trusses	Hips	Jacks	Valley/Saddle Done	Beams	Acc Bundles	Fixings	Floors
2	Ready	walls, Main trusses,Beams	Done	done				DoNE
3	2 products missing	walls, Main trusses,Beams,fff	Done
4	Unknown State	all
5	1 products missing	jaCKS, BeAMS,flooRs				done					done
Feuil3

Cell Formulas
Range	Formula
K2:K5	K2	=LET( tableOfProducts,$M:$U, requiredProducts,$L2, headerTable,TRIM(LOWER(CHOOSEROWS(tableOfProducts,1))), listRequiredColumn,REDUCE("",TEXTSPLIT(requiredProducts,",",,TRUE),LAMBDA(lVal,rElm,TEXTJOIN(";",TRUE,lVal,IFERROR(MATCH(TRIM(LOWER(rElm)),headerTable,0),"")))), listValues,CHOOSECOLS(CHOOSEROWS(tableOfProducts,ROW()),VALUE(TEXTSPLIT(listRequiredColumn,,";",TRUE))), IFERROR(IF(AND(LOWER(UNIQUE(listValues,TRUE))="done"),"Ready",IFERROR(SUM(--(LOWER(listValues)<>"done"))&" products missing","Not ready")),"Unknown State"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
2:5	Expression	=$K2="Unknown State"	text	NO
2:5	Expression	=$K2="Ready"	text	NO
2:5	Expression	=$K2<>"Ready"	text	NO

Bests regards,

Vincent