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

Why does my header formula change as I add rows? I don't want it to change.

My header needs to display a number, which is calculated as follows: B2-B3.


Whenever I add a row, the header formula automatically changes to B3-B4. I don't want that to happen. I need the formula to remain as B2-B3 as I add rows.


I tried using $B$2 and $B$3 instead, but that didn't help. The formula still changed.






This is the original formula when I had two rows:





As I added a third row above, the formula changed:




How can I prevent it from changing?


Thank you in advance.

MacBook Pro 16″

Posted on Dec 27, 2022 2:43 PM

Reply
Question marked as Top-ranking reply

Posted on Dec 27, 2022 6:08 PM

Try this:

Formula shown is entered in B1. Row 1 is a Header row.


Below:

I selected cell B2, then pressed option-up arrow to insert a new row above row 2,*

then entered the new value (500) in column B of the (new) row 2.


*I chose B2because I wanted to confirm that doing so would NOT make any change to the content of the then current B2 (now B3). The easy insert row(s) using option shift works independently of the content of the selected cell, and creates as many new rows as there have been in the (single column stack of) cells selected.

A similar process, using cells selected in a row, then pressing option-left or right arrow, can be used to insert new columns in a table.


As shown, there is no change in the original formula, which continues to reference the cells in the (current) rows 2 and 3.



Regards,

Barry

2 replies
Question marked as Top-ranking reply

Dec 27, 2022 6:08 PM in response to Pooshka

Try this:

Formula shown is entered in B1. Row 1 is a Header row.


Below:

I selected cell B2, then pressed option-up arrow to insert a new row above row 2,*

then entered the new value (500) in column B of the (new) row 2.


*I chose B2because I wanted to confirm that doing so would NOT make any change to the content of the then current B2 (now B3). The easy insert row(s) using option shift works independently of the content of the selected cell, and creates as many new rows as there have been in the (single column stack of) cells selected.

A similar process, using cells selected in a row, then pressing option-left or right arrow, can be used to insert new columns in a table.


As shown, there is no change in the original formula, which continues to reference the cells in the (current) rows 2 and 3.



Regards,

Barry

Why does my header formula change as I add rows? I don't want it to change.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.