Transpose

[ukat101052]

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 respectiv
worksheet. In A3 is the word "Day", while in rows A4:A34 are the number
1 through 31 (corresponding to days of the month), in the date forma
"1/1/2012", etc. In row 3, columns B:F contain dates of the first day o
a respective, consecutive month and year spanning ther period 1/1/201
thhrough 3/31/2013. For example in B3, the date 1/1/2012 appears, whil
in C3, 2/1/2012 appears, etc. all the way to P3 the date 3/1/201
apppears. In the "body" of the table (B4:F34) is daily dat
corresponding to the respective day, month and year.

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

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

Thanks in advanc

 

[zvkmpw]

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 isto 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.