I am trying to create a workbook that I can use to easily record labor efficiency based on a number of variables.



I need to be able to enter some of the data and have corresponding data pulled for me from a table.

I want to be able to enter the part and sequence numbers that the person worked on for the day and have the corresponding crew size and rate filled in for me based on what I enter for the part and sequence numbers.

Is there a way to do this?

Attached Files

work worksheet.xlsx (14.5 KB, 21 views)

Definitly a way to do this but you have a lot of fields on your spreadsheet. If you just want formulas to fill in results on the crew size and rate columns then I guess all we need to know is an example. What would you put in for the seq and part and what result do you want to come out on the rate and size.

List at least 5 examples so a formula can be created. Again it shouldnt be too hard once we get this information.

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, 16 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, 19 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

I really need help in figuring out how to use the IF function and VLOOKUP together. All of these formulas need to be placed in the Order Form worksheet of my Excel workbook. I'd really appreciate anyone helping me set up the formulas and explaining the proper way to do these. I have attached my workbook.

This is the question in my Excel workbook that I am having trouble with:

In cell C17, insert an IF function that tests whether the value in cell B17 is equal to an empty text string (" "). If so, display an empty text string; otherwise, use the VLOOKUP function to look up the name of the item corresponding to the item ID in cell B17 in the Product_List table. Specify an exact match. Copy the formula in cell C17 to the range C18:C22.

In cell D17, insert an IF function that tests whether the value in cell C17 is equal to an empty text string. If so, display an empty text string, otherwise, use the VLOOKUP function to look up the price of the item in the Product_List table. Again, specify an exact match and copy the formula in Cell D17 to the range D18 : D22.

In cell F17, insert an IF function that tests whether the value in cell C17 is equal to an empty text string. If so, display an empty text string; otherwise, multiply the item price in cell D17 by the quantity in cell E17. Copy the formula in cell F17 to the range F18:F22.

Attached Files

Vincent_ExCrADDCase3.xlsm (18.8 KB, 13 views)

Scheuerman1987, this sounds more like a help-me-understand question than a please-solve-my-problem question, so I'm going to ignore your workbook and switch to Socratic questions:

1) Do you know how the IF function works by itself? If you do, give an example of an IF call; if you don't, say so and I'll explain.

2) Do you know how to use the VLOOKUP function by itself? If you do, give an example or explain it in your own words; if not, tell me as much as you DO understand about it and I'll fill in the gaps.

Once we've gone over the two pieces, it should be easy enough to explain how to use them together—if by then you still need us to.

Well I know how to use the functions separately. It's nesting them that I am trying to figure out so that the formula works.

The IF Function: IF(logical_test, [value_if_true,] [value_if_false])

Ex: IF(C33>=$L$11, $L$10, 0)

So that's saying if L11 = 1000
L10 = 800

So if cell C33 is greater than or equal to the value in cell L11, the formula returns the value in cell L10.

If the value in cell C33 is less than the value in cell L11 the formula will retunr a 0.

VLOOKUP Function: VLOOKUP(Lookup_value, Table_array, Col_Index_num, Range_lookup)

EX: VLOOKUP(Years Service, G4:H8, 2)

So this would look at the employee's years of service. It would look at G4:H8 because that is where you told Excel to look and that the information or award you want returned is stored in column 2. You don't have to use the range_lookup if you omit that then it makes VLOOKUP an approximate match lookup.

With all that said I just need to know how I'd go about nesting the 2 functions together.

Ok, just checking. So the first IF function is supposed to check $B$17; if it's blank, then the IF statement should return a blank, otherwise it should do a VLOOKUP in some table for the value in $B$17. The IF, then, says

=IF($B$17="","",lookup)

I don't know what the exact VLOOKUP is, but let's pretend it looks like this:

=VLOOKUP($B$17,OtherWorksheet!$A:$C,3,0)

(That searches in column A of a worksheet named OtherWorksheet, looking for a value that matches $B$17 in this worksheet; if it finds a match in A27 (say), then it returns the value in C27.)

To combine them is simple; you just put the VLOOKUP call inside the IF call, where I wrote "lookup" before, like this:

=IF($B$17="","",VLOOKUP($B$17,OtherWorksheet!$A:$C ,3,0))

Note that you don't duplicate the '='; that's not actually part of the function call, it only introduces to Excel that you're about to do a calculation of some kind. But everything else is copied exactly, including all the parentheses.

Make sense?

Thank you so much for explaining how to nest these two functions together. It made it a lot easier for me to figure out this assignment and complete it!! I always get confused when it comes to nesting. I had never seen these two functions used together. I don't think my text book explained nesting very well either.