Transpose
 

Posted this on Mr Excel.

I have several worksheets in one workbook entitled, "A", "B", "C", "D", and "E". In A1 of each worksheet is the title of the respective worksheet. In A3 is the word "Day", while in rows A4:A34 are the numbers 1 through 31 (corresponding to days of the month), in the date format "1/1/2012", etc. In row 3, columns B:F contain dates of the first day of a respective, consecutive month and year spanning ther period 1/1/2012 thhrough 3/31/2013. For example in B3, the date 1/1/2012 appears, while in C3, 2/1/2012 appears, etc. all the way to P3 the date 3/1/2013 apppears. In the "body" of the table (B4:F34) is daily data corresponding to the respective day, month and year.

What I would like to do is "transpose" this data to a "Master" worksheet set up as follows: in row B1:F1 would contain the names of the individulal worksheets (A, B, C, D, and E). Range A2:A457 would contain daily dates beginning with 1/1/2012 and ending with 3/31/2013. In the "body" of this worksheet, i. e., B2:F457 would be the daily data corresponding to the respective day, month, year and worksheet.

Any ideas (either VBA or worksheet functions would suffice)?

Thanks in advance

Transpose
 

I have several worksheets in one workbook entitled, "A", "B", "C", "D",
and "E". In A1 of each worksheet is the title of the respective
worksheet. In A3 is the word "Day", while in rows A4:A34 are the numbers
1 through 31 (corresponding to days of the month), in the date format
"1/1/2012", etc. In row 3, columns B:F contain dates of the first day of
a respective, consecutive month and year spanning the period 1/1/2012
through 3/31/2013.
In the "body" of the table (B4:F34) is daily data
corresponding to the respective day, month and year.
*
What I would like to do is "transpose" this data to a "Master" worksheet
set up as follows: in row B1:F1 would contain the names of the
individual worksheets (A, B, C, D, and E). Range A2:A457 would contain
daily dates beginning with 1/1/2012 and ending with 3/31/2013. In the
"body" of this worksheet, i. e., B2:F457 would be the daily data
corresponding to the respective day, month, year and worksheet.
*
After filling in row 1 and column A of the Master sheet, one possibility is to put this in B2 and extend it to all of B2:F457
=INDIRECT(B$1&"!"&
CHAR(CODE("A")+MONTH($A2)+12*(YEAR($A2)-YEAR($A$2)))&
DAY($A2)+3,
TRUE)

This assumes the tab names are A,B,C,D,E.

Hope this helps getting started.