Creating an excel template to input production and yield data for manufacturing

I would like to create a template to manage production and yields. So that at the end of the day, an operator comes and will input all the production information from the day and record it. The purpose is to analyze production over time and its yields. We have various components like operators, machines, processes, material, etc so I want to know where we can be more efficient and why we had problems if production is low. Also, it is important to know what defects we have so that we can analyze and find the root cause of these problems.

It is important to mention that the users of this template are not that tech or excel savvy thus the template must be very easy, intuitive and automatic. The less manual work the better.

I have created a 3 sheet template.

1. Master Sheet. This sheet contains all the variables that the user can input. So users can not type whatever they want, they always have to choose from the lists. For example when they choose the shift, they will have a dropdown with (1,2 and 3) as options. For Category they have (SWprocesses and Lidprocesses). This lists will modify with time, for example we will add and eliminate operators, products, etc through time.

We have dependent variables and others that are independent.

Depended variables are category of products, within each category you have different families of products, within families you have different part numbers, within each category you also have a list of processes for SW and for Lids, for each process you have machines, for each machine you have operators. All these are connected.

The rest are independent variables.

2. Input sheet. This is the only sheet that is going to be used by the operator. This is the most important sheet because it has to be easy for the user to input the information as quick as possible. So I would like to automate some parts, for example if you choose as a category SW processes then to display in the different rows all the processes that go with that category so that the operator has to input only the quantities, choose the operator, the parts and so on.

So what I would like in this sheet is to have a set of columns where the operator will input information that comes from various lists and at the end it will have a button to upload all this info to a data table. Information like like what date is it, what shift is it, etc. etc. All these fields are detailed in the excel file attached. So I would like to try to automate the input and facilitate so that data can be entered correctly so that it can be analyzed afterwards.

3. Database is where all the information is stored. Thus I would like to use pivot tables to analyze this database.

Please let me know if you understand my requirement.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

---

Want to get your question answered quickly?

• Ensure your question is not too vague. Don't assume anyone is familiar with your problem. While you can upload small attachments, describe your problem in the body of the post. We are fortunate to have several Excel gurus, but few mind-readers.
• On the other hand, skip irrelevant details. Be descriptive and concise. Short, direct, and to-the-point questions with apt thread titles are almost always answered promptly.
• Keep the scope reasonably narrow. Questions like, "How do I set up an accounting system in Excel?" might be a long time waiting.
• Explain what you've already tried. ("Calculation is set to automatic, but formulas still don't compute") so helpers don't waste your time or theirs.
• Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.