Attn: Tom Ogilvy (Please)

[Teerings3]

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

[Tom Ogilvy]

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",OFFSET(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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Teerings3]

both of your assumptions are correct, but I get a Name#? error

[Tom Ogilvy]

formula was copied from a worksheet where it was producing the correct
results. I would suggest your formula has a typo.

--
Regards,
Tom Ogilvy


"Teerings3" <[email protected]> wrote
in message news:[email protected]...
>
> both of your assumptions are correct, but I get a Name#? error
>
>
> --
> Teerings3
> ------------------------------------------------------------------------
> Teerings3's Profile:
http://www.excelforum.com/member.php...o&userid=29560
> View this thread: http://www.excelforum.com/showthread...hreadid=492859
>