Transpose question

[Jays]

I have a MASTER summary worksheet with this layout

07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total
No of late deliveries 36 40 20 10 9 115
No of Drivers 18 32 11 11 4 76


getting its info from WEEKLY worksheets eg

03-Sep 04-Sep 05-Sep 06-Sep 07-Sep Total
No of late deliveries 7 8 7 7 7 36
No of Drivers 4 3 1 6 4 18


I would like the master layout to be like :


No of late deliveries No of Drivers
07-Sep 36 18
14-Sep 40 32
21-Sep 20 11
28-Sep 10 11
05-Oct 9 4
Total 115 76


But as the values in the MASTER worksheet are linked to the weekly
worksheets, Is there anyway i can Transpose these linked values and still
have them linked, and the chart associated with the Master table picking up
its data from the newly arranged data source?

 

[Bernard Liengme]

We could give a more specific answer if you told us the formulas in the
Master sheet
best wishes

 

[Max]

Think you could transpose it directly from the source table in your Master

In your Master summary sheet,
Assume source table below is in A1:G3

07-Sep 14-Sep 21-Sep 28-Sep 05 - Oct Total
No of late deliveries 36 40 20 10 9 115
No of Drivers 18 32 11 11 4 76

You could put in say, A6:
=INDEX($A$1:$G$3,COLUMNS($A:A),ROWS($1:1))
Copy A6 across / fill down to C12 to return the required transpose which is
dynamic to the source in A1:G3. Format A7:A11 as dates.

 

[Herbert Seidenberg]

You can generate the table *and* chart
from the weekly data using Pivot Table.
No formulas required.
Follow the Pivot Table Wizard to see how
it was done.
http://www.freefilehosting.net/download/3a66d

 

[Jays]

Thanks Guys for all your replies, I guess ran out of time and eventually
used the correct terminology in google "transpose formulas" which took me to
a page using a sub procedure.

 

[Max]

For thread completeness & closure, and general benefit of all,
could you also post the subroutine? solution that you found/used here?

... we could also see how far our guesses were out