Consolidate

[rich987652]

I have a workbook with 5 worksheets, one for each of the past 5 years. I have used the Consolidate function from the Data menu to produce a total worksheet. This has provided me with the details I require and it has grouped the 5 individual amounts onto each line as expected. However, I want to be able to drill down the grouping to see the figures for each year on any line easily.

However, if I drill down then each cell in the left column is blank. Ideally I wish to have these cells populated with the worksheet names that the data has come from. Any idea of how I can do this? I can copy and paste but this will take a fair amount of time as it is a large worksheet.

If anyone can help, I would appreciate it. Hope I've explained myself clearly enough.

 

[BrianB]

My solution would be to put all data into a single worksheet or Access table, add a "Year" column, and use AutoFilter or Pivot Table etc.