Okay, after all the thread confusion...
Here's a solution to summarise ALL data in one normalised table. It relies on referring to an external workbook, and combines data from all worksheets:
Whilst this does normalise your data, it is rather inefficient, as all the dimension attributes of the claim are repeated on every fact row. What would make more sense is to create a data model, with a detailed fact table, and a summary dimension table:
Load these to the Data Model (PowerPivot), and create a relationship between them:
Now you can report against this data model quite efficiently.
See attached workbook for examples.
In all queries above, I've deliberately left the code somewhat verbose, to make it easier to follow each step. There's a bit of optimisation we could apply, though.