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?


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.)

  • An additional question: could I do the same way when using =importrange ? – mimi lee Aug 9 '16 at 9:10
  • If yes, what would be the function? I tried something like =importrange(indirect("xxxxxxx", "sheet1! &A1") but it becomes #N/A. Please tell me no matter it is possible or not. Thanks! – mimi lee Aug 9 '16 at 9:15
  • With importrange it's easier because the range reference is already a string, you don't indirect. It's just =importrange("key", "sheet1" & A1) – user79865 Aug 9 '16 at 13:15
  • Really thank you for the help, just one last question- if I want to change all reference sheets synchronously (i.e. change all "sheet1!" to "sheet2!"), is it possible to do? – mimi lee Aug 10 '16 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.