Assume the downlaod worksheet is in the same workbook and has the name
Download. Put this formula in F2 and drag fill down the column:
=OFFSET(Download!$A$1,MATCH('OT
Report'!A2,Download!A:A,0)+1,MATCH("Productive",OF FSET(Download!$A$1,MATCH('
OT Report'!A2,Download!A:A,0)+1,0,1,20),0))
If the employee ID is not found or the the word productive is not found, it
will return #N/A.
Also, this assumes that the employee ID is stored as a number in both OT
Report and Download. If not, you could get a #N/A error as well.
--
Regards,
Tom Ogilvy
"Teerings3" wrote
in message ...
I have a worksheet named (OT Report) it looks as follows:
A = Emplyee # B= Supervisor C= FirstName D= Last Name E= Scheduled
Hours F= Worked Hour
example data
A B C D F
41531441 Open Deb Albr 36
9725217 Patty Glor Bat 40
120615975 Patty Lin Bea 40
105815219 Open Eliza Bris 24
12276142 Open Br Bro 40
37284952 Pat Lake Bro 15
143421680 Kathy Sar Bro 40
The other worksheet is called (Download), it does not have header it is
just a download of converted data it looks like this:
41531441 Albr, Deb R. FIXED CLASS: DA002
HRS: 51.5 WORKED 60 NON-WORKED 23.25 PAID
BY PAY CODE PRODUCTIVE 60 WEEKEND DAY
120615975 Bea, Lin M. FIXED CLASS: DA002
HRS: 57.5 WORKED 50 NON-WORKED 16 PAID
9725217 Bat, Glor G. FIXED CLASS: DA002
HRS: 73.75 WORKED 56.75 NON-WORKED 21 PAID
BY PAY CODE PRODUCTIVE 56.75 WEEKEND DAY
Now here is what I need:
Employee# from (OT Report ) to find a match on (Download) and once
found to look the range 2 rows down and 7 columns over and find the
word "Productive" once found look 1 cell next to and return that value
to column F of (OT Report). The reason I can not just count over once
it matched employee # id is the protuctive worked might not always be
in the same cell with every download but will be in at least one cell
in the 2 rows down and 7 columns over.
Do yoy know anything that will work Formula or code?
Thanks
Edit/Delete Message
--
Teerings3
------------------------------------------------------------------------
Teerings3's Profile:
http://www.excelforum.com/member.php...o&userid=29560
View this thread: http://www.excelforum.com/showthread...hreadid=492859