jolivanes
05-27-2008, 10:45 PM
I have a Job forecast sheet which requires tool allocations for different job types.
There are 5 different job types of which 2 can be neglected (No tools required).
Of the remaining 3 job types there are several possibilities which tools will be needed.
These are the criteria:
If Job type = D and Toolsize = Big, then 2 Big Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium, then 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Small, then 2 Small Wrenches + 2 Small Screwdrivers
If Job type = D and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 2 Medium Screwdrivers + 2 Small Screwdrivers
If Job type = L and Toolsize = Big, then 2 Big Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium, then 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Small, then 2 Small Wrenches + 1 Small Screwdriver
If Job type = L and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 1 Medium Screwdriver + 1 Small Screwdriver
If Job type = H and Toolsize = Big, then 2 Big Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium, then 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Small, then 2 Small Wrenches + 3 Small Screwdrivers
If Job type = H and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 3 Medium Screwdrivers + 3 Small Screwdrivers
These are the criteria for one job but there could be as many as 50 jobs going in one day. Is it possible at all to get a total of different tools needed for each day with a formula or is VBA needed?
In the attachment I have calculated tools required manually but I would not know where to start with a formula or with VBA.
Any help is greatly appreciated.
Thanks and regards.
John
There are 5 different job types of which 2 can be neglected (No tools required).
Of the remaining 3 job types there are several possibilities which tools will be needed.
These are the criteria:
If Job type = D and Toolsize = Big, then 2 Big Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium, then 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Small, then 2 Small Wrenches + 2 Small Screwdrivers
If Job type = D and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 2 Big Screwdrivers
If Job type = D and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 2 Medium Screwdrivers + 2 Small Screwdrivers
If Job type = L and Toolsize = Big, then 2 Big Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium, then 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Small, then 2 Small Wrenches + 1 Small Screwdriver
If Job type = L and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 1 Big Screwdriver
If Job type = L and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 1 Medium Screwdriver + 1 Small Screwdriver
If Job type = H and Toolsize = Big, then 2 Big Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium, then 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Small, then 2 Small Wrenches + 3 Small Screwdrivers
If Job type = H and Toolsize = Big and Toolsize = Medium, then 2 Big Wrenches + 2 Medium Wrenches + 3 Big Screwdrivers
If Job type = H and Toolsize = Medium and Toolsize = Small, then 2 Medium Wrenches + 2 Small Wrenches + 3 Medium Screwdrivers + 3 Small Screwdrivers
These are the criteria for one job but there could be as many as 50 jobs going in one day. Is it possible at all to get a total of different tools needed for each day with a formula or is VBA needed?
In the attachment I have calculated tools required manually but I would not know where to start with a formula or with VBA.
Any help is greatly appreciated.
Thanks and regards.
John