 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Transpose question
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? We could give a more specific answer if you told us the formulas in the Master sheet best wishes  SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I have a MASTER summary worksheet with this layout > [quoted text clipped - 22 lines] > have them linked, and the chart associated with the Master table picking > up its data from the newly arranged data source? 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. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- >I have a MASTER summary worksheet with this layout > [quoted text clipped - 22 lines] > have them linked, and the chart associated with the Master table picking > up its data from the newly arranged data source? 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 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. >I have a MASTER summary worksheet with this layout > [quoted text clipped - 22 lines] > have them linked, and the chart associated with the Master table picking > up its data from the newly arranged data source? 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 SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.