Copying Data Between Sheets

**python023** · 02-20-2009, 10:49 PM

I have two spreadsheets in the same workbook.
Sheet1 contains statistics for teams and years.
Sheet2 contains a listing of the teams in different orders.

How can I have excel copy a teams stats from Sheet1 to Sheet2 as it goes down the row? For example, A1 is atlanta 1996 so it copies a section of atlanta's 1996 stats to a specific range. Then A2 is detroit 2002 so it copies detroit's 2002 stats, and so on and so forth all the way down the list?

I am a beginner with VBA but I have experience with C/C++. I am just not sure of the functions for excel do all these things automatically and am having trouble finding applicable examples.

Thanks!

**NBVC** · 02-20-2009, 10:58 PM

Attaching your own sample with some expected results would help determine the best route.

**python023** · 02-20-2009, 11:07 PM

Sheet 1
A B C D
team year stats1 stats2
atl 1996 50 51
det 1998 25 65
mia 2000 60 44
mia 2001 12 17

Sheet 2
A B C D
team year stats1 stats2
det 1998
atl 1996
mia 2001

I want excel to see that the team and year in sheet 2 are detroit 1998 and go to sheet 1 and copy stats1 and stats2 from sheet1 to sheet2. Then it sees the next entry, atl 1996. It finds atlanta's 1996 stats and copies them to sheet 2. So the final output will be:

Sheet 2
A B C D
team year stats1 stats2
det 1998 25 65
atl 1996 50 51
mia 2001 12 17

**NBVC** · 02-20-2009, 11:15 PM

try, In Sheet2!C2:

=Index(Sheet1!C$2:C$100,Match(1,(Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=$B2),0))

Adjust the ranges to suit your Sheet1 data... and then confirm the formula with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear..

Then copy the formula to D2 and then copy both formulas down the columns.

**iammetx** · 02-20-2009, 11:29 PM

Highlight the cells in sheet 1 that you want to copy to sheet 2. On sheet 2, click on cell where you will paste your formula, then go to Edit in the menu bar up top and select Paste Special. Once you get the pop up window for Paste Special, select Paste Link. Now, everytime you edit your cells in Sheet one, they will change on sheet 2, but only if you did a paste link for them. Hope this helps.

**python023** · 02-21-2009, 01:33 AM

Originally Posted by nbvc

try, in sheet2!c2:

=index(sheet1!c$2:c$100,match(1,(sheet1!$a$1:$a$100=$a2)*(sheet1!$b$1:$b$100=$b2),0))

adjust the ranges to suit your sheet1 data... And then confirm the formula with ctrl+shift+enter not just enter. You will see { } brackets appear..

Then copy the formula to d2 and then copy both formulas down the columns.

thank you so much!