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

Question

Level 1

14 points

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

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

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

Reply

Answer 2

Pooshka Author

Level 1

14 points

Dec 27, 2022 6:33 PM in response to Barry

Barry, you're simply the best!

Reply

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

Similar questions