Link and transpose data from multiple sheets into a summary

Hi all,

I'm new to the forum so hopefully this question isn't redundant. I tried searching for something similar, but didn't exactly know what to search. I'm also new to VBA and macros so please bear with me. (Please also note this is not my actual data and is simply a hypothetical situation that would represent the same circumstances, so please forgive me if the math for number of cells doesn't add up correctly in this example.)

I'm creating a database and would like to create charts from a summary sheet linked to the changing data. I have six different sheets that are all set up identically, however have data from different sources, lets call these sheets by years, so 2000, 2001, 2002, 2003, etc. For instance cells B2:G2 in sheet "2000" are the mean, median, mode, max, min, SD, and count for January 2000 sales. Cells B3:G3 in sheet "2000" are the mean, median, mode, max, min, SD, and count for February 2000 sales. Similarly, cells B2:G2 in sheet "2001" are the mean, median, mode, max, min, SD, and count for January 2001 sales.

Now I would like to create a summary sheet that takes the data from each individual sheet and lines it up side by side, so all of the statistics for January 2000-2005 are lined up side by side to create a chart. For example, I would like to take cells B2:G2 from sheet "2000" and link them to cells C3:C7 in sheet "summary." Then I would like to take cells B2:G2 from sheet "2001" and link them to cells D3:D7 in sheet "summary." Then I would like to take cells B2:G2 from sheet "2002" and link them to cells E3:E7 on sheet "summary." This would be repeated for six sheets. The data may also be updated in each individual sheet, so I would like to have the summary linked, rather than just cut and paste. After January 2000-2005 is summarized in cells C3:H7 in the summary sheet, I would like to put the February summary below it in cells C10:H16.

Is it possible to setup a macro to do this? Right now I am having to go into each individual cell on the summary sheet, press "=" then click on the corresponding cell in a different worksheet. I have six worksheets in total with around 20 variables in each, and each variable has a set of 7 statistics, so it really ends up eating up a lot of time the way I am doing it now. So what I am really trying to do is line up all 7 of the statistics, from each variable in each sheet, side by side, to create a chart.

I really appreciate any help! Thank you!

Thank you for the response. That makes much more sense. Attached is a mockup spreadsheet of what I am trying to do (Excel Mockup.xlsm). The numbers in sheets 2000-2005 are all randomly generated. I started filling in the summary sheet manually, but would like to know if there is an easier way to do it.

I appreciate any help!

Thanks again!

Dustin

Thank you oeldere! Much appreciate!

Could you please explain the syntax behind the formula? I'm having trouble applying the formula for the other months. For instance, how would the formula change to do the same thing for the February and March data?

I'm assuming the &B$2& is grabbing the sheet name from the top of the column, but what does the Char(63+Row())&"2" do? I've also never seen the "&" used in formulas before. Could you please explain that as well?

Thanks again!

Yes. That worked great! Thanks again!

Another VBA, but the Months have to be merged manually, from B-H column