Hello. I am designing a work book that will compare a set minimum, maximum, usage, and on hand amount for parts and techs. The min and max tabs will have about 250 columns of part names and about 150 rows of technicians names. Where they intersect will be a static number to make the minimum and maximum. Each tab will be identical except for the static number at the intersections. I will get a usage download showing the date an item was used, the work order, the tech name, and the item name. I will also get an on hand download showing item, quantity, and tech download. The issue tab will be identical to the min and max tabs except that the intersection will be a formula that looks up
The actual formula following the above logic works but it is a mouthful.
That big formula works but it takes some time since on a busy day it will be in 37500 cells. The reason it is that way is so I can see at a glance which parts and which techs are going to be heavy that day. A power query script unpivots the Issue table. That query is pivot tabled in tabular form showing the tech at the top and the items, scan codes, quantity to issue below. Then I break out the page report filter by name option, select all the pivots, print to pdf and mail out to the picker crew.
It works and I have learned a lot but I am wanting to see if there are other techniques I can learn that might be more efficient. I can already see the absolute references will cause issues when adding or deleting techs and parts across three tabs. So I don't expect someone to solve my issue. I'm more looking for pointers and references so I can work the issue.
Thank you very much for reading and for any advice you'd care to offer.
- the minimum (its currently a two part xlookup on the min tab)
- the maximum (same as the min just a different tab name)
- Usage via a sumifs based on tech name, part, and a date range
- On hand via countifs based on part name and tech name
Excel Formula:
=IF(USED+ON HAN<MINIMUM,MINIMUM-ON HAND,IF(USED+ON HAND>=MAXIMUM,MAXIMUM-ON HAND,USED))The actual formula following the above logic works but it is a mouthful.
Excel Formula:
=IF(COUNTIFS(USED!$M:$M,$A2,USED!$F:$F,D$1)+COUNTIFS('OH FIELD'!$I:$I,$A2,'OH FIELD'!$A:$A,D$1)<XLOOKUP($A2,MIN!$B$1:$DO$1,XLOOKUP(D$1,MIN!$A$4:$A$186,MIN!$B$4:$DO$186,0)),XLOOKUP($A2,MIN!$B$1:$DO$1,XLOOKUP(D$1,MIN!$A$4:$A$186,MIN!$B$4:$DO$186,0))-COUNTIFS('OH FIELD'!$I:$I,$A2,'OH FIELD'!$A:$A,D$1),IF(COUNTIFS(USED!$M:$M,$A2,USED!$F:$F,D$1)+COUNTIFS('OH FIELD'!$I:$I,$A2,'OH FIELD'!$A:$A,D$1)>=XLOOKUP($A2,MAX!$B$1:$DO$1,XLOOKUP(D$1,MAX!$A$4:$A$186,MAX!$B$4:$DO$186,0)),XLOOKUP($A2,MAX!$B$1:$DO$1,XLOOKUP(D$1,MAX!$A$4:$A$186,MAX!$B$4:$DO$186,0))-COUNTIFS('OH FIELD'!$I:$I,$A2,'OH FIELD'!$A:$A,D$1),COUNTIFS(USED!$M:$M,$A2,USED!$F:$F,D$1)))That big formula works but it takes some time since on a busy day it will be in 37500 cells. The reason it is that way is so I can see at a glance which parts and which techs are going to be heavy that day. A power query script unpivots the Issue table. That query is pivot tabled in tabular form showing the tech at the top and the items, scan codes, quantity to issue below. Then I break out the page report filter by name option, select all the pivots, print to pdf and mail out to the picker crew.
It works and I have learned a lot but I am wanting to see if there are other techniques I can learn that might be more efficient. I can already see the absolute references will cause issues when adding or deleting techs and parts across three tabs. So I don't expect someone to solve my issue. I'm more looking for pointers and references so I can work the issue.
Thank you very much for reading and for any advice you'd care to offer.
