2

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?

Improve this question
1

1 Answer 1

Reset to default
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.)

Improve this answer
4
  • An additional question: could I do the same way when using =importrange ?
    – mimi lee
    Aug 9, 2016 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, 2016 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, 2016 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, 2016 at 2:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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