I have been looking for hours now how to solve my problem, as it follows :

I want to duplicate this master sheet a hundred times and be able to let the end-users fill in the green cells, without breaking the IMPORTRANGE formula (cell A3).

Also keeping the original formulas (additions and multiplications) of the master sheet within the copied sheets would be a great plus.

I have tried multi things with QUERY formulas but I just can't sort it out.

Thanks in advance to anyone helping out !

  • "Thanks in advance to anyone helping out !" Have you tried the given solution? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Commented May 20, 2020 at 18:05

1 Answer 1


Do the following:

  1. Make a copy of tab Feuille 1

The rest of the steps are all in this copied tab.

  1. In cell A3 place the formula

=IMPORTRANGE("1hwZCXxC_beJ7mWas0Wl5bUcOw7l7sm3NrO9Czk1oCs4";"Feuille 1!A3:R7")

  1. In cell A14 place:

=IMPORTRANGE("1hwZCXxC_beJ7mWas0Wl5bUcOw7l7sm3NrO9Czk1oCs4";"Feuille 1!A14:R20")

  1. Manually fill in the values for cells A8, A9-R9, A11-B11.

  2. Go to Data >> Protected sheets and ranges and lock the sheet except cells B8-R8.

  3. Delete the first sheet keeping just the copy.
  4. Make 100 copies of your TEST sheet.
  5. Enjoy :)

Your Answer

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

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