Newbie with “IF” problems

Joined
Feb 7, 2025
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • IMG_5546.png
    IMG_5546.png
    183.2 KB · Views: 16
  • IMG_5547.png
    IMG_5547.png
    73.6 KB · Views: 15
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
KLMNOPQRSTU
1Production StatusRequired productsWallsMain TrussesHipsJacksValley/Saddle DoneBeamsAcc BundlesFixingsFloors
2Readywalls, Main trusses,BeamsDonedoneDoNE
32 products missingwalls, Main trusses,Beams,fffDone
4Unknown Stateall
51 products missingjaCKS, BeAMS,flooRsdonedone
Feuil3
Cell Formulas
RangeFormula
K2:K5K2=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
CellConditionCell FormatStop If True
2:5Expression=$K2="Unknown State"textNO
2:5Expression=$K2="Ready"textNO
2:5Expression=$K2<>"Ready"textNO


Bests regards,

Vincent
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,917
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top