Vlookup Issues

**whatthe** · 09-21-2009, 03:11 AM

First off I have already attached the excel document in question.

I am trying to make an excel sheet that will help make it easier to schedule job for the company I work for.

In my document I have to sheets, the first one is labeled Scheduling and the second is labeled Data. I am using the vlookup command to pull the information from the data sheet to display on the scheduling sheet.

What I’m doing is entering a job number under the Part column in the Scheduling sheet and getting the vlookup value for the stock that would be used on the job. Now where my issue begins when I enter a value under the Run Quantity column. Using this value “=VLOOKUP(A2:A25,Data!$A2:$H224,8)” I am to get the job run time for that Part number problem is that the only way to get a value for that part I have to enter that part number on the same line its listed in the data sheet.

I.E. If the first job I want to schedule to be run that day is 1405 and I enter it in line 2 under the part column and enter 2000 under quantity you get nothing but if you enter the same information on line 7 you will get the value for the run time. I am not sure how to fix this. Please take a look at the attached sheet and let me know what you think
.

**DonkeyOte** · 09-21-2009, 03:24 AM

Check your use of absolute & relative references (see XL Help if unsure)

F2: =VLOOKUP($A2,Data!$A$2:$H$224,8)
copied down

On an aside be aware that using TRUE rather than FALSE for your range_lookup paramater in the VLOOKUP will be fine as long as:

a) all parts are listed
b) all parts are listed in sequential order on Data tab

...having looked further the fundamental issue is ... the Run Time on Data is affected by Run Quantity on Scheduling sheet so as you alter E (Scheduling!E7) you affect the total Run Time (Data!H7) for that affected part.

**Rob Moyle** · 09-21-2009, 03:26 AM

I may be misunderstanding what you're asking, but I think I've spotted a few things to look at.

You're currently using formulas that are trying to lookup a 'range' rather than a single cell. Ie: =VLOOKUP(A2:A25,Data!A2:H224,2,TRUE)

Also, the range that you are looking up (Data!A2:H224) is not fixed. That is, when you fill down your original VLOOKUP formula to rows 2, 3, 4 etc, the range you are looking up changes to A3:H225, A4:H226 etc.

Try the following in cell C2, instead:

=VLOOKUP(A2,Data!$A$2:$H$224,2,TRUE)

When filled down to rows 3, 4 through to 25, the first cell reference (lookup value) will change accordingly to cells A3, A4, through to A25. The $ signs in the range you're looking up will ensure that you're always looking up range A2:H224 on the data sheet.

**whatthe** · 09-21-2009, 03:41 AM

All of my issues are in the "Run Time Column" When we do the scheduling for any particular day its with random jobs we may need part 1405, 1402, 1401 in that order but when you look at the data sheet all of the jobs listed there are in numerical order. What I need to figure is our to get the run time listed in the line the part is listed on no matter what order the jobs are listed in the data sheet.

With the way I have things working now the only way to get the time to display in the run time column is to list the job in the scheduling on the same line its listed in the data sheet.

**DonkeyOte** · 09-21-2009, 04:16 AM

I confess I don't follow what you're trying to do with your calcs but if we assume that on sheet Data you want Column H to be:

= Quantity / Cut / 1400

where Quantity is determined by value input on Scheduling tab (if at all) then:

Data!H2:
=SUMIF(Scheduling!$A$2:$A$25,$A2,Scheduling!$E$2:$E$25)/$D2/1400
copied down

If you then correct the VLOOKUP in column F on Scheduling sheet as outlined previously (re: use of absolute references) then you "should" get what you want (?)

**whatthe** · 09-21-2009, 04:47 AM

Thanks you have solved my issue with the
=SUMIF(Scheduling!$A$2:$A$25,$A2,Scheduling!$E$2:$E$25)/$D2/1400 formula

I had already attempted to use absolutes as you previously suggested but I was still having the same issue.

Now all is almost well and I need to get to bed

Thanks again