Inserting Cells loses Formula

Hi,

I have a problem when I insert cells into a worksheet, in that there is another sheet that looks up data from this sheet, that then ignores (skips) the inserted data.

I have attached two xlsx documents to show this.

Excel-Before-Insert.xlsx is the original before I insert any rows. The Summary sheet calls data from the Raw Data sheet and displays it. I have used a simple formula in the Summary sheet and if you click down the cells from A2 you will see the formula gets the data from RawDataA2, RawDataA3, RawDataA4 etc.

Excel-Aftere-Insert.xlsx is the same sheet, but I have inserted a blank row in RawData row 3. I want this blank row to show in the Summary but it doesn't. If you click down the cells from A2 in the Summary you will see the formula gets the data from RawDataA2, RawDataA4, RawDataA5, so it misses RawDataA3 - as that is the inserted row.

Is there anyway to stop this happening, so my Summary in Excel-Aftere-Insert.xlsx would look like this with a blank row for the inserted cell.

Client Revenue
Coke £10,000.00

Pepsi £2,000.00
7 Up £4,000.00

NB - this is just a very small set of example data, the real xlsx I have the problem is has the Summary sheet as it ignores some columns and also has graphs.

Thanks
Jennsy