Forum Discussion

packie's avatar
packie
Brass Contributor
Jul 20, 2024

How to maintain a fixed range when cell is added

 

 

=SUM(J10:J58+G10:G58)

 

The above formula works perfectly fine

 

However, each week an additional cell needs to be inserted in J10:J58

 

This causes the formula to return an error

 

For example:

 

J34 is moved down one cell by performing the step Insert/Shift Cell Down.

 

By doing this my original formula changes from:

=SUM(J10:J58+G10:G58)

to:

=SUM(J10:J59+G10:G58)

 

To fix it I manually change it back to

=SUM(J10:J58+G10:G58)

 

How can I prevent J58 changing to J59 when a cell is inserted into the range.

 

  • packie 

    You can use the INDIRECT function for this purpose:

     

    =SUM(INDIRECT("J10:J58"), G10:G58)

     

    If you also want to keep G10:G58 fixed:

     

    =SUM(INDIRECT("J10:J58"), INDIRECT("G10:G58"))

  • packie 

    You can use the INDIRECT function for this purpose:

     

    =SUM(INDIRECT("J10:J58"), G10:G58)

     

    If you also want to keep G10:G58 fixed:

     

    =SUM(INDIRECT("J10:J58"), INDIRECT("G10:G58"))

Resources