Forum Discussion
packie
Jul 20, 2024Brass Contributor
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.
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"))
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"))