Forum Discussion
The problem with ranges in formulas
Good afternoon, I need help! When you create a formula with a reference to another sheet, when you change the range of the other sheet, Excel itself changes the range in the original formula. How to prevent Excel from doing this?
You can use INDIRECT to prevent that.
For example, let's say you have a formula
=SUM(OtherSheet!A1:A10)
Change it to
=SUM(INDIRECT("OtherSheet!A1:A10"))
Since the argument of INDIRECT is a text string, it won't change if you move or delete the range A1:A10.
2 Replies
You can use INDIRECT to prevent that.
For example, let's say you have a formula
=SUM(OtherSheet!A1:A10)
Change it to
=SUM(INDIRECT("OtherSheet!A1:A10"))
Since the argument of INDIRECT is a text string, it won't change if you move or delete the range A1:A10.
HansVogelaar Thank you very much
