How to modify several range references in a Spreadsheet synchronously?

Question

There are 20 cells that have functions like this:

A1 =index('sheet2'!$A$1:$B$3)

C1 =index('sheet3'!$A$1:$B$3)

E1 =index('sheet4'!$A$1:$B$3)

So in each function, only the sheet number changes, but the range remains.

My problem is:
I want to change the range (e.g. from A1:B3 to C1:D3) of all functions, but don't want to change them one by one, is there any convenient ways to modify all of them at once?

Answer 1

You can make the range reference a string parameter by using indirect. Example: cell X1 contains text $A$1:$B$3, cell A1 contains the formula

=index(indirect("Sheet2!" & X1))

and similarly for other cells. Then if you change the content of X1, all range references change accordingly.

(You don't actually need the index part if the formula is as it appears here, but I suppose your real spreadsheet had more parameters.)