Summary report thru multiple sheets

[ushaanu]

hi all,

I need one summary report thru multiple sheets , actually I have data in multiple sheets now I need one summary like

No. of Dealers Visited in this MonthNo. of ealers Visited YTDNo. of Customers visitedNo. of Customer Contacted on phone

basically I create months sheets now I need latest month data automatically update in summary report .

thanks in advance

Anu

 

[Luke M]

Before tackling this issue, are you still in the workbook design phase? If so, I'd strongly suggest changing from having multiple worksheets with raw data to a single sheet with data.
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm#Layout

If you had all the information on a single sheet, it would take 5 seconds to create a PivotTable which could do all the analysis that you are currently wanting.

 

[ushaanu]

hi Luke ,

I understand pivot table is best option for data summarizing but one of dppt wants month on month different sheets and one summary report ,

and when fill data in latest month sheet automatically update summary report .

anu

 

[Luke M]

Very well. Ultimately, you still need to get all the data into a single location, so we can use this merge macro to get all the weekly sheets together:
http://www.rondebruin.nl/win/s3/win002.htm

Then, base the PivotTable off of that data. In the attached, I put in the macro, modified it to grab the range of each month's sheet, grouped it together, and built the PT. You could create a button or something to run the macro if you want.

 

[Vivek D]

Another option (if you have 2010 or above) is to use Power Query to combine all of the sheets into 1 and then pivot based on that.

 

[ushaanu]

Thank you so much