On sheet1 the rows all defined by the person doing whatever job they are assigned for the day.

So, for an example of what I am hoping to be able to do, if we look at cell A2, (Susan,) the values for cells B2:G2 would be entered manually. Then based on what the values are in cells C2 and F2, values for cells H2 and K2 would be determined for me using the lookup/if function to look at the table that is included starting at T4 ending at Y18.

I hope that makes sense. This is starting to make me go at little looney trying to piece it together on my own. My excel knowledge is mostly self taught from reading forums like these, and some beginner level courses I took through my work.

Any help would be most appreciated. I re-saved and attached the workbook in question and filled in some of the values on sheet1 in hopes it can do a better job showing what I am hoping to accomplish than my long winded typed explanation may be doing now.

Attached Files

work worksheet.xlsx (230.5 KB, 18 views)

Ok there are some fairly complex formulas that will accomplish this however my suggestion is to add 2 columns that you will hide that takes both the part and seq and puts it into 1 string. Then you just need to write a very common vlookup to accomplish what you want. Otherwise what you want is a formula that performs a lookup based on 2 criteria. I have attached your spreadsheet with edits to show the column idea as well as the vlookups. If you autofill it down remember to lock the numbers for your vlookup formulas.

Hope this helps.

Attached Files

worksheet w edits.xlsx (28.8 KB, 21 views)

In case you really do not want to create the hidden columns here are the 2 formulas

For the Crew size
=IF(C2=4600,VLOOKUP(F2,W$5:Y$11,2,0),VLOOKUP(F2,W$ 12:Y$18,2,0))

For the Rate
=IF(C2=4600,VLOOKUP(F2,W$5:Y$11,3,0),VLOOKUP(F2,W$ 12:Y$18,3,0))

The problem with this if you have more than 2 part number it will not work. You could write an addtional formula at the beggining of the if formula but again I think it would be easiest with the 2 columns.

Lastly you may want to look into adding a dropdown list for the job field to speed things up but after you enter the same job twice it will autofill in.

Let us know what you come up with.

Think I will definitely go with the hidden columns.

WOW. Thank you very much this was EXACTLY what I was trying to do.

I will add the list for the part numbers and the equipment I think. The example I attached was just of 2 part numbers and just some basic operations. There is another workbook where I can pull data from to expand what I started with. The company has hundreds of parts numbers and hundreds of processes. And, now that you have shown me how to do this, there are a few other workbooks I have in mind where i can simplify the way we store as well as analyze our data.

Very glad to help. Feel free to mark as solved