Good day everyone,
This is my first question on this forum, forgive me if its long since I may not have enough excel vocabulary to explain what I need.
I will be as specific as I can so that the situation can be grasped.
My problem is as follows-
goal: to measure cost effectiveness by determining total time of a product being made by making a table that calculates work time.
whats been done: In the first worksheet, there is a list of finished products (restaurant dishes) in one table. Next to that table I had created a table in which the left column provides a list of "all variable time procedures" in the kitchen, and on the right column, "all fixed time procedures" in the kitchen. For example, as a chef, Id put all actions in the kitchen such as "chopping 1kg of celery for 2 mins" and "kneading dough for 10 mins" on the left column, and actions such as "oven baking @ 350 degrees for 20 mins"or "let stand for 10 mins" on the right column.
After all the different tasks in the kitchen were put in that table, I would now like to be able (in another worksheet) to be able to chose specific dish (using a validation dropdown) in a cell and have another table populate with all the relevant procedures chosen from the other worksheet in SEQUENTIAL order (wash hands, chop vegetables, pre heat stove, ect...) down the columns (both variable and fixed)-along with this, a TOTAL amount of time spent on making the dish.
where im stuck: In the second worksheet, I created a table that provides 6 columns. 1) variable time procedures 2) quantity, 3) time in mins, 4) fixed time procedures, 5) quantity) and 6) time in mins.
In the second worksheet, I want to be able to VLOOKUP an item from the list of finished products in the first worksheet with data validation, then populate 4 columns- variable time procedure, time in mins, fixed time procedures, time in mins. The 2 columns left are for me to plug in quantity as desired. Combinations of procedures from the table in the first worksheet are unique to specific dishes in the list, and so when I use a data validation dropdown and chose a dish, only the appropriate procedures get populated in the fixed/variable columns. In addition to this, I wish to account for work that can still be done while something is cooking. For example, Im not going to wait until something on the stove is finished cooking before I start preparing the garnishing. This is where im having difficulty adding up the entire job time because I cant just add the totals of the "variable" and fixed" procedures and expect a realistic total time required to finish the job. And the last problem would be if I wanted to make a dish for 2 or 6 people. I would type in a quantity in the "qty column" and expect to multiply the task accordingly, but this should not apply with the "fixed time" in the oven when looking at the cell that provides the total work time.
In the end, Id like to create a cell that provides total time for a dish to be made and a table that separates fixed and variable time procedures specific to that dish-all the while, accounting for jobs that can be done while another tasks are in progress.
I hope somebody understands what it is I am trying to do here and can help me out! It would be a world of help to me.
Thanks,
Mr Nat.
Bookmarks