Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attn: Tom Ogilvy (Please)
I have a worksheet named (OT Report) it looks as follows: A = Emplyee # B= Supervisor C= FirstName D= Last Name E= Schedule 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 i 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 onc found to look the range 2 rows down and 7 columns over and find th word "Productive" once found look 1 cell next to and return that valu to column F of (OT Report). The reason I can not just count over onc it matched employee # id is the protuctive worked might not always b in the same cell with every download but will be in at least one cel in the 2 rows down and 7 columns over. Do yoy know anything that will work Formula or code? Thanks Edit/Delete Messag -- Teerings ----------------------------------------------------------------------- Teerings3's Profile: http://www.excelforum.com/member.php...fo&userid=2956 View this thread: http://www.excelforum.com/showthread.php?threadid=49285 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tom Ogilvy (Please)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attn: Tom Ogilvy (Please)
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Attn: Tom Ogilvy (Please)
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
attn: Tom Ogilvy cell data in header | Excel Programming | |||
ATTN: Mr. Bob Phillips | Excel Worksheet Functions | |||
ATTN: Tom Ogilvy or anyone else: send DOS command? | Excel Programming | |||
Attn LoriM | Excel Discussion (Misc queries) | |||
ATTN: KEEPITCOOL | Excel Programming |