Grab data from one workbook and copy to report style workbook

**noidea_4** · 11-13-2012, 05:34 PM

Hello Excel Gurus,

I'm asking for one simple macro, I'm sure it doesnt take long. I"m going to explain it in a very simple form.

I have two workbooks: Workbook #1 is list with ALOT of Raw Data, but i'm going to simplify it as so:

First Name	Second Name	Social
Joe	Harris	12345
Mark	Smith	678945
Jeff	White	45698
Camilla	Johnson	45879

Workbook number 2 is a Report style workbook. The end user will input the social security number for which data its needs. using that social number the macro would identify it on the workbook #1 and gather the data from that specific row and copy it to the workbook number 2. EXAMPLE: Let's say I need the info from social "12345", macro will find "12345",grab First Name "joe" and Last Name "Harris" and put it in cells...A3 and C5 in workbook number 2.

Is this clear? Again, in this example its just 2 fields i'm transfering info to workbook number 2. BUT in reality its over 25 fields that are being transfered from the Raw data, onto the Report format workbook.

Thanks!!!

**K m** · 11-13-2012, 06:15 PM

You could use INDEX and MATCH
For example:
B1= Lookup Criteria
Array of Data: A3:C7
Social Security Data: C3:C7

=INDEX(A3:C7,MATCH(B1,C3:C7),1)

=INDEX(A3:C7,MATCH(B1,C3:C7),1) would return first name
=INDEX(A3:C7,MATCH(B1,C3:C7),2) would return last name

An example is attached

**noidea_4** · 11-13-2012, 06:35 PM

Thanks! But It's a bit more complicated (I believe). Attached is the Workbook #2, so the report. All the Raw Data from workbook#1 would be matched and then plotted into its corresponding cells. So, again i gave you a very simple example, on the report...it would march value by "Number" (O3), grab from Raw Data and copy it to, let's say, the "Overall" (C7) cell. With your INDEX, how would I move about different columns and cells.

Thanks again!!

**K m** · 11-13-2012, 07:24 PM

To make the formula dynamic, instead of hard coding the columns, ie 1,2,3,4, etc., you could use the column (COL) function and copy the formula to the left

**noidea_4** · 11-14-2012, 10:26 AM

Can you give me an example?

**K m** · 11-14-2012, 10:39 AM

Suppose we take the formula and want to make it relative so we can copy it to the right:

Start with this:
=INDEX($A$3:$C$7,MATCH($B$1,$C$3:$C$7),1)

Change to this:
=INDEX($A$3:$C$7,MATCH($B$1,$C$3:$C$7),COLUMN(A:A))

As you copy the formula to the right Column A becomes Column B, Column C, Column D, etc.

More Precisely
=Column(A:A) =1
=Column(B:B) =2
=Column(C:C) =3