Alerts when X ammounts of items arrive for project X
Backround:
I work with custom computers, its only me myself and i in my department so i am looking to automate as much as possible. I have a sheet where i enter every part that arrives and which Project number it belongs to, what Serial number the part has and the parts EAN code.
Goal:
I would love if i could set up a systems that automatically pings me when all parts have arrived for a certain project. Right now every piece gets its own row, so im thinking i could have a helper sheet with all the project numbers and how many Parts each projects need. So when X ammount of rows exist for Project X, i get a ping or email. But how can i do this automatically 😅
Ive gotten a report sheet to show me how many parts exist for each project, but cant figure out my function and cant find anyone else here in the community who has done the same thing.
Best Answer
-
Hi @EdCT,
You're absolutely on the right track with this. If your main sheet is for number of items received against all projects ( and your helper sheet has the total number required already filled in, then you can use a COUNTIF (or COUNTIFS if you want to check multiple things) formula using cross sheet references to check.
For example, you have 2 projects (A123 and B456) with 4 components each. Your receipted delivery sheet ends up looking something like this:
Your project totals would be something like this:
The formula in the "Components delivered" being:
=COUNTIF({Project Number}, Project@row)
For the cross sheet reference in the { } brackets, you need to set this up (no copy paste!) - if you're not sure how then this should help:
The "All components arrived" formula is:
=IF([Total components required]@row = [Components delivered]@row, 1, 0)
You can then set up a workflow automation with this as a basic framework:
Once all components are arrived for a project, you would then get an notification telling you the details:
Obviously you can tinker with the message and so on.
Hopefully this gives you some guidance, but if you've any other problems/questions then just let us know!
Answers
-
Hi @EdCT,
You're absolutely on the right track with this. If your main sheet is for number of items received against all projects ( and your helper sheet has the total number required already filled in, then you can use a COUNTIF (or COUNTIFS if you want to check multiple things) formula using cross sheet references to check.
For example, you have 2 projects (A123 and B456) with 4 components each. Your receipted delivery sheet ends up looking something like this:
Your project totals would be something like this:
The formula in the "Components delivered" being:
=COUNTIF({Project Number}, Project@row)
For the cross sheet reference in the { } brackets, you need to set this up (no copy paste!) - if you're not sure how then this should help:
The "All components arrived" formula is:
=IF([Total components required]@row = [Components delivered]@row, 1, 0)
You can then set up a workflow automation with this as a basic framework:
Once all components are arrived for a project, you would then get an notification telling you the details:
Obviously you can tinker with the message and so on.
Hopefully this gives you some guidance, but if you've any other problems/questions then just let us know!
Help Article Resources
Categories
Check out the Formula Handbook template!
