I've created a monthly spreadsheet for my work site. It's for use on all 3 shifts starting with nights, mornings then afternoon. Each tab is a day and named for the date. I have a main data sheet, a Summary sheet for the statistics to update in real time and the days of the month. I also have a "Start" Tab as well as an "End" Tab as I have a VBS to fill the sheet with copies each named for the date sequentially.
In our shop ,we have 10 pieces of equipment to maintain, each one has a unique alphanumeric name. Each of these units has 2 subunits, an input filler and then a QC filler that is composed of 25 different holders, all simply numbered 1-25. These holders tend to wear out quickly and fail so I'm trying to create a spreadsheet to monitor the usage. We don't use a lot of them, but they're expensive and multiple issues can be indicative of a bigger problem. Worst case scenario would be to have to change 5 or so over the course of a shift.
I've created separate drop down lists for the necessary data as the rest of the staff is not excel friendly at all. I currently have the first dropdown list available in cells A3 through A7, it is the list of all machine names so the staff tech can select which one had an issue.
The next set of cells, B4-B7 has the option to choose "Fill" or "QC". The final drop down list is just numbered, #1-25. the tech can choose what number filler was changed. I have alotted 5 cells across for potential issues on one machine and end. So the numbered dropdown list is in cells C4-C7 and also across from C to G. I have totals using sum on each of the sides and bottoms to keep track of the total number of stations changeed and it updates on the front Summary Sheet as it is changed in the book.
On the Summary sheet, I have the totals for fillers changed. I also have a list of the machine numbers on the left and to the right I have a cell for filler and QC.
I want to keep a tally of each type of what specific fillers have been changed and how many times. I am trying to create a script for the front page that will keep track of the choices selected in the dropdown lists in each page and reflect the data in the appropriate cell on the summary sheet.
Such as, the 30 days between start and end, how many times Machine A1 had a QC filler changed for filler #4?
maybe the top ten repeat fillers below?
Please let me know if you have any questions. I can use all the education I can get, I'm still a "noob" with excel. Any ideas of a better way to track them is appreciated!!
