Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: NMBob
NMBob Author
User level: Level 1
49 points

Freezing column formulas?

I have a simple three column sheet where the second column is durations (HH:MM:SS) and the third is, basically, elapsed time. C2 is the sum of B1:B2, C5 is the sum of B1:B5, etc. Is there a way to freeze the column 3 formulas so if I pick up a row and move it the formula for that row doesn't move too? It looks like if I drag row 5 up to above row 3 the formula for what was C3 gets adjusted to be the sum of B1:B4, but the fomula for the new C3 is still the sum of B1:B5. Thanks!

iMacs, MacBooks, minis, iPods, iPads, etc.

Posted on Apr 1, 2015 8:21 AM

Reply
Question marked as Best reply
User profile for user: Wayne Contello
Wayne Contello
User level: Level 7
26,529 points

Posted on Apr 1, 2015 8:59 AM

You will have to use sort-safe formulas. This means the look a little more complicated.


I would recommend something like this:


in cell C1 place the formula:

=SUM(OFFSET($B$1,0,0,ROW(), 1))


User uploaded file


now fill C1 down by selecting C!, copy

select column C, paste


now you can drag rows around as needed

View in context
2 replies

Freezing column formulas?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up