Deleting rows leads to #ref

[Jaspal]

Hi I have a table of which gets bigger or smaller depending on number of inputs. One of the columns is a sumation of individual cells, when i delete a row however this leads to #ref as the cell it is looking for is no longer there. Is there anyway of avoiding this.

Thanks

[TheExcelphile]

You could look at using OFFSET.

To learn how to do that, go here.

Don't forget to click on the little star at the bottom left of this post if you feel I helped!

[Norie]

What formula are you using for the sum?

[Jaspal]

HI I have two columns a + b

the formula in the sum is Sum((a1*b1) + (a2*b2) + (a3*b3)) etc

[Norie]

Is that copied down all the rows of the table?

[Jaspal]

Hi Norie

Yes its is , same formula all the way down.

[Norie]

Can you attach a sample workbook?

[Jaspal]

HI Noorie

I have attached spreadsheet.

Problem occurs in (cashflow tab) Cell I96 an M196 whenever I delete a row.

This also leads to issues further down.

So for example, if i delete Row 17 I also need to delete Rows 127 -134 manually (I dont know how to link the two tables together. This then gives me a Ref in T142

Thanks

[Norie]

Do the formulas in B196 to H196 not get affected?

[martindwilson]

one thing i have noticed is that
=((M17*J17)+(M18*J18)+(M19*J19)+(M20*J20)+(M21*J21)+(M22*J22)+(M23*J23)+(M24*J24)+(M25*J25)+(M26*J26)+(M27*J27)+(M28*J28)+(M29*J29)+(M30*J30)+(M31*J31)+(M32*J32)+(M33*J33)+(M34*J34)+(M35*J35)+(M36*J36))/(J38-J37)
can be just
=SUMPRODUCT(M17:M36,J17:J36)/(J38-J37)

[Jaspal]

HI Noorie

Will make that change, thanks. And from that how would I use offset i that context?

Just looking at your previous comment to see what else goes wrong when i delete rows.


Many thanks fr your help!

[Jaspal]

HI Noorie

Your sumproduct worked and solved the problem, Thanks the issue I have now is when I delete say rows 127:134 it leads to refs in the cells bellow, which I cant seem to understand as there arent any links,

[Jaspal]

HI Please

ignore as I have realised where the link was!

[martindwilson]

i seem to be noorie now