+ Reply to Thread
Results 1 to 11 of 11

Populate a table with options and lookups

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    2007
    Posts
    9

    Populate a table with options and lookups

    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.
    Last edited by JBeaucaire; 07-09-2013 at 09:21 PM. Reason: Corrected the title, as per Forum Rules. Please take a moment to read the forum rules, link is in the menu bar above.

  2. #2
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Hi Mr Nat.
    I don't understand that "chopping 1kg of celery for 2 mins" is a variable, surely it's a fixed time procedure? If I was to attempt something like this, I would start with a recipes table, a preparation table, and a cooking time table. If I understand correctly, your prep is your fixed times(for recipes), and your cooking time(for recipes) is your variable?
    I mean if it takes 2mins to chop 1kg of celery, then it takes 4mins to chop 2kg., which to me is a fixed time. This could reflect from your recipes table for how many people you are catering for, in a table heading.
    As it is at the moment, you only have finished dishes, and if you don't know what is in them then how can you attempt to do a prep time?

    Perhaps I am looking at it from another aspect? But hey! if it helps...great, if it doesn't....no harm done.
    Pete.

  3. #3
    Registered User
    Join Date
    06-29-2013
    Location
    Quezon City, Philippines
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Populate a table with options and lookups

    This is what you can do.

    Insert a Name for each dish on the first worksheet, then define (ALT+I+N+D).

    Eg.

    Dish1 = Refers to (your sheet name)!(cell range)

    Names in Workbook = Dish1
    Refers to = Sheet1!$A$1

    Do the same for the other dishes. Once you have everything set, create a name for the entire selection of dishes.

    Eg.

    Names in Workbook = List_of_Dishes
    Refers to = Sheet!$A$1:$A$10

    Go to the next worksheet and create your data validation. Choose criteria as "List", type in =List_of_Dishes (defined name). Dropdown will show the dishes...

    Soon as you have the lookup value (Validation), then incorporate the vlookup formula on the succeeding columns.

    Hope this helps...

  4. #4
    Registered User
    Join Date
    07-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Populate a table with options and lookups

    Thanks for your reply.

    The chef example is a perfect example of what im trying to accomplish and so
    I chose something like chopping celery as variable, because different people cut at different times. The purpose of the table intended is to measure the performance of emplyee work times and measure their performance based on salary. Its also meant to see if certain products are worth having on the menu depending on the labor effort and price of the ingredients. Also, If i was going to cook soup for a hundred people, I would try to find a way to cook at large volumes at a time to fit that demand, rather than cooking single portions of soup a hundred times, chopping a hundred times ect... chopping one celery stick at a time vs 2 kgs at a time.

  5. #5
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Aahhh, I see. You are measuring prep time performance and end cost. Could you upload your workbook, so we have a better grasp on what you have done?

    Goto edit, advanced, and click the paperclip icon.

    Pete.

  6. #6
    Registered User
    Join Date
    07-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Populate a table with options and lookups

    Quote Originally Posted by Pierre11 View Post
    Aahhh, I see. You are measuring prep time performance and end cost. Could you upload your workbook, so we have a better grasp on what you have done?

    Goto edit, advanced, and click the paperclip icon.

    Pete.

    Heres what Im trying to do, Im trying to have a table populate all the relevant procedures that are related to the items being selected. All of the possible procedures are found in worksheet 1. Once an order is placed in the dropdown(s) option in workbook 2, I want a table to display all the materials and procedures necessary to fulfill that order.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Hi Mr Nat.
    I have copied most of your stuff to a new workbook, and am uploading it.
    As I don't know what ingredients (apart from the obvious) you include, I have only done coding for 1 item for the combo box.(It is the 1st item on the list). You will find the code in the vba editor.
    If this is the sort of thing you want, then fill in the table on the very right of sheet 2.
    There is obviously a lot more coding to be done, to get the final result you want (I'm not the best coder around...lol), but any feedback will be good.

    Pete.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Hi Mr Nat.
    Perhaps this is more to your brief.

    Pete.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-09-2013
    Location
    Quebec, Canada
    MS-Off Ver
    2007
    Posts
    9

    Re: Populate a table with options and lookups

    Yes, thanks for your time!

    You've done it. Quite impressive- You knew exactly what needed to be done. This is what Ive been trying to to do with excel. A whole new world for me to learn here! How is it possible to have 2 columns side by side that show variable and fixed time procedures and then add a quantity field next to the dropdown that will correlate with them? In the sheet 2 (your workbook returned), it shows a total time for all actions. Can it be such that fixed and variable procedures could be isolated side by side? The reason being for these columns to ideally be next to each other is that it only takes one "fixed" action to cook 9 beef patties, but many actions in dressing the burgers in demand that will exhaust those patties on standby. A Big Mak trio surely doesnt take 10.73 mins to make if we were to order it. But if nothing is on standby, then 10.73 mins is what someone will wait for if nothing is ready (deep frying fries, and cooking patties). The total times for fixed and variable procedures would perhaps be better separated to view the performance of "labor" and the performance of the machines(ovens fryers) independently. The amazing thing with this chart that you helped me out with is that we can finally start to see what and where these costs are... and where a business like this can start to lose money and customers- a chart like this could apply to so many other scenarios.


    I found your work in the VBA editor and after viewing it, havent got the slightest clue where to begin when it comes to accomplishing the above. Do you know where I can start? The chart that you returned on sheet 3 has fields that were left empty because I assume there is no cost for material or ingredients for some of those actions. You requested that I should fill them? If that was what was intended, then I would have to say that those blanks have already been taken into account in other actions (crowning sandwitch cost was already accounted for in "toasting bun") In sheet 1 cell G2, I should of simply named it "cost of material" instead of "... variable material". There are 2 cost categories, material (wrapper, boxes), and ingredients (ketchup, ect...) Most actions already have either one of those cost cells associated with it, if not, they are simply left blank because the only cost would be the employee salary/hr- something that will be calculated later... This is where the employee column(Employee A, B, C) comes into play. It would be ideal to have their worktimes isolated so that we can compare employee effort and of course in the end, use this to guage salary. This stuff is amazing. Can you show me where to start with all of this?

  10. #10
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Hi Mr Nat. I am only just learning vba, mostly by my mistakes...lol.
    I know the problem you have, I saw it as I was doing the simple code. You are right in saying that the table shows whether an operation is specific to the food item. The thing that governs everything tho', is the Order for the meals. This accounts for all of the operations, and as you say, cooking 9 burgers is the same time as cooking 1 burger. I think you need a userform to input the order to assembly the operations more realistically.
    Are you saying you want employee categories like Front of House, Kitchen, and Finishing? Front of House would be employee A, Kitchen...employee B, and finishing...employee c.
    The other thing you mention is " patties on standby". Do you have a standard where you always have patties/fries on standby? and if so, then you will have batch runs in cooking?
    I have only just started using dataforms and userforms ( mainly the Excel dataform, cos most of the work is done for you), but I'll have a go.
    If anyone with more experience than me wishes to help, then perhaps I can learn from them
    My simple code is all rem'd.
    Just looking at it again, and I'm wondering whether or not a pivot table would be more appropriate, of which I know very little about.
    Pete.
    p.s the chart on sheet3 was just something I was playing with. It has no significance other than looking at the employee situation. Unless of course something like that would benefit you?
    Last edited by Pierre11; 07-19-2013 at 10:27 PM.

  11. #11
    Registered User
    Join Date
    07-02-2013
    Location
    Shropshire,England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Populate a table with options and lookups

    Hi mr Nat.

    On sheet 2, press the place order button, and you will be presented with a userform. Use this to input your orders and click the button 'Yes please'. It seems to work well with the operations/actions, but the time is still unrealistic.
    I think this is because of the cooking/frying times. Would it be advisable to split the frying time of fries by the portions 2kg gives?
    Also, I have semi assumed what each employee does, as I have a column for each employee time.
    I have put some notes around the sheet about what I am doing and if I am wrong about which employee does what, can you put them in Column B next to where I put the operations next to the lower table?

    Please bear in mind that I am not a great coder, but I manage to get most simple things done somehow!
    No doubt somebody could halve my code, but I can't .....yet!

    Regarding the variable and non variable objects you require; I will come to that later. Let's get one thing sorted at a time and working well, then we can move on to the next requirement.

    Pete.
    Attached Files Attached Files
    Last edited by Pierre11; 07-21-2013 at 09:26 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1