If I have a formula in A1 that reads: =sum(a2:a5000) to get the sum of up to 5000 rows. Is it possible to have that value remains as 5000 after I insert or delete some rows? TIA.

You can use the =INDIRECT function: In Cell B1 enter A5000

In Cell A1 enter =SUM(A2:INDIRECT(B1))

MIKE

No need to "waste" B1. =SUM(A2:INDIRECT("A5000"))

Keep in mind that in either case, you can't mess with Row 2. You will get a #REF error if you delete Row 2 or the Formula will change to =SUM(A3:INDIRECT("A5000")) if you insert a Row at 2.

If you want to lock in both the upper and lower cell, use this:

=SUM(INDIRECT("A2"):INDIRECT("A5000"))

Thank you for both of your input. I didn't know what INDIRECT was all about, and now I doâ€¦Thanks again.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History