Match

[Teerings3]

I have worksheet "OT Report" that looks like this:

EMPLOYEE # SUPERVISOR F NAME L NAME SCH WORKED
41531441 Open Deb Albr 36.00
9725217 Patty Glor Bat 40.00

120615975 Patty Lin Bea 40.00
105815219 Open Eliza Bris 24.00
12276142 Open Br Bro 40.00
37284952 Pat Lake Bro 15.00
143421680 Kathy Sar Bro 40.00

I have another worsheet "Download" that looks like this:

41531441 Albr, Deb R. FIXED CLASS: DA002
HRS: 51.5 WORKED 51.5 NON-WORKED 23.25 PAID
BY PAY CODE PRODUCTIVE 51.5 WEEKEND DAY
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
120615975 Bea, Lin M. FIXED CLASS: DA002
HRS: 57.5 WORKED 41.5 NON-WORKED 16 PAID

I need a code that looks for a match for employee# from OT Report on
the DownLoad and once it finds the match then finds the word Productive
from the a range associated with the cell location of the match, then
get the value of the cell next to the word productive and put it in the
cell under worked .

 

[Guest]

Hi,

No need to write VBA for that ....

you could use Worksheet formulae on the worksheet like this:

=INDIRECT(ADDRESS(MATCH(A:A,Sheet2!A:A,0)+1,4,4,1,"Sheet2"))

(goes in column F on your sheet OT Report!)

first we search for a match in column 1 (Employee no) on the sheet 2 column
1 and that returns 1 for the first one (meaning row 1)

then with the result of that, we use ADDRESS

And we pass the ADDRESS into the INDIRECT function to return the value we
want.

the three formulae, in colums G, H, I would be like this:

=MATCH(A:A,Sheet2!A:A,0)

returns 1 (for 1st row)

=ADDRESS(G:G+1,4,4,1,"Sheet2")

(notice I add one to G2, to offset the row by 1)

this returns an address Sheet2!D2

then in column I use INDIRECT:

=INDIRECT(H:H)

for Deb Albr it returns 51.5

BTW, this INDIRECT function need the source sheet containing the hours to be
open, or you could copy it to a temp sheet then refresh the formula, or you
would have to use the custom 'MoreFunc.xll' external ADD-IN by Laurent Longre
(see http://xcell05.free.fr/english/ )

HTH

Philip

 

[Teerings3]

Thanks that worked for my model, but the location of the wor
"productive" will vary
with each new download of employee time punches. Plus what ever I us
to get the hours of "pruductive" , I will then use to find PTO for tha
employee or OVERTIME and EIB, and so on..............The only constan
is that after you match employee# that key word will be found befor
the next employee # or the result is "0"