Tracing contributors to sum total

I have a large worksheet which gives me totals, for the different sectors i need to sum, through the DSUM function, and then the sums of those too....

my data is by necessity spread accross many pages, is there a way of calling from a totalled sum function, a list of its contributing figures and other details from the data rows about those figures.

the problem is when there is a figure which may be unexpectedly large, short of combing through a massive amount of data manualy, i have no way of seeing which are the largest contributing factors to the total.

eg if house F had £3000.00 how would i get it to tell me that 2500 of it was a new boiler cost for example.

is it possible???

It is not easy to give you the correct answer to you problem without seeing you actual spreadsheet, but I can give you some ideas.

Tip 1
You could use conditional formating to set the background color of your cells when a specific condition is met.

Tip 2
Are you familiar with Pivot Tables? There are a chance that your results could be created with a pivot table instead of using the DSUM functions. In a Pivot Table there is a handy function where you can double click on a result and get a list of the contributing data. Just what you ask for !

I have tried the pivot table solution and as you pointed out this will provide me with the fuction that i need, the only problem being that as my data is spread over 100 pages and many rows on each, when i tried to consolidate this into a pivot table it freezes the program.

If i am asking too much of pivot tables i dont know as i am not familiar with them. is there something i'm not doing that will enable a pivot table to handle this much data or possibly another solution.

Does anyone know a solution to this problem???

The only suggestion I have would be to look at the Auditing menu. The Show Precedects will show you which cells contribute to a particular cells value. Which will help you create the category subtotals you want.