Referencing the Cell Above

I need to make my forumla's reference the cell above it, even when I insert a new row. Right now, I have cell F5 as the =SUM(F4-C5). This works fine, but the spreadsheet as a ton of rows and if I add a new row near the top, I want it to automatically formulate the ones below to match the inserted row. If I can make the formula reference the cell above always, it should work. I know this is possible but I can't figure it out!

Nick's picture

with the formula in

with the formula in B5

=C5

can be written as:

=OFFSET($C$1,ROW()-2,0,1,1)

 

.. NOTE: this is a solution, but it's not good design as it may slow down your sheet significantly