Retrieving Offset Values from Secondary Workbook based on Names

[Kouk]

Hello. I'm new to these forums and I apologize in advance for what appears to be a fairly common topic. I'm also new to Excel however, and while I've looked through several Searches and read 35+ posts here and elsewhere, and got a Textbook, I've only gotten as far as "Ooh, maybe that would work..." but I can't quite wrap my head around getting what I need done.

Hopefully, you great, nice, super, awesome people will be able to help


I am trying to extract cell values from one Workbook, and put it into the cell of another workbook. If it was a small amount of items that would be pretty simple, but there are over 100 entries each Workbook. I'm using Excel 2010 on Windows 7, but the files are older and have to work on different platforms running Excel. What I'm trying to do is extract the hours worked by person X, on Worksheet Y, from a separate workbook and worksheet, and display them in the first book and sheet for payroll purposes.

The problem is this: I need a single formula (or several similar ones, or a VBA script, or a Macro I guess) which will go into an Origin Cell in "Workbook 1," "Worksheet 1," then be able to look to its Left in the Same Row to see a Text String (a name), then look into a SECOND WORKBOOK, "Workbook 2" "Worksheet 2" to match that String exactly listed in Rows in Column B.

THEN, after doing that, starting from that name's Row in Column B in "Workbook 2," it looks a set number of Rows AND Columns to the Right and Down to find a Value, which the Origin cell displays in the first workbook.

And then two similar formulas only different in which cells they are referencing.


In a perfect world, there would be a Macro that could activate multiple copies of this formula (for different names all on the same sheet) to look for the data, AND look in multiple outside Workbooks, either automatically or on a separate Macro.



I am not allowed to make substantial changes to the source files or I would rearrange things to make this simpler. VLOOKUP won't work on its own, because I have to look Down several Rows after the match. I've considered Match and Index but I just don't understand enough about writing Excel code to get it to work I'm afraid, and I get a #N/A value not available.


My current code is this:
=INDEX((MATCH("Smith, John", ='[Workbook 2.xls]Worksheet2'!$B:$D, 0)), 4, 10) Again, it's supposed to check the name to its (edit)left, in this case Smith, John, but that step I am also not sure about.

I was also looking into INDIRECT, ADDRESS, and OFFSET but still confused.

In the Workbook 1 that I want data to be filled into, Names are always 3 Rows to the LEFT, and are not unique, but they are always in their own Rows. There could be any number of names, currently over 100.

In Workbook 2, Names are Unique and in their own Rows. Hours are always exactly 3 cells Right and 9 cells Down from any given Name. If there isn't any Value in that target cell, a return of "0" would be preferable. Or if VBA is the way to do this...?

Any help would be greatly appreciated. This would save many hours a day doing this manually ><;

Here are two sample files of the same design I'm dealing with:Attachment 148665Attachment 148666 To and From fields are highlighted blue.

Please Ignore the Workbook 2 that is 856.5kb, not sure how to remove it but it has macros I didn't strip out.

[Kouk]

Or a suggestion on which formulas may be ideally suited to this sort of thing? Or VBA or macros?