-1

Background
I usually add several sheets to a single Google sheets file. There is always a separate sheet that extracts information from the other sheets (via REGEXEXTRACT etc).

Problem
sometimes this "summary" sheet has sensitive information in it that I don't want shared with everyone, and I would like to simply copy it and paste it into a different Google sheets file that's shared with a specific set of people. The problem is that if I simply make a copy and paste.. all the cells that references other cells get an error since they now reference cells that don't exist.. How do I handle this?

  • I'm downvoting this question as it doesn't show any research effort. – Rubén Oct 12 '16 at 13:46
0

Short answer

To convert local references to external references use IMPORTRANGE

How

  1. First you have to get the spreadsheet key / file id or the URL to the original spreadsheet
  2. Wherever your sheet has a local reference that should be converted to an external reference, use IMPORTRANGE to convert it to an external reference

Example

Change

=Sheet1!A1

to

=IMPORTRANGE(spreadsheet_key,"Sheet1!A1") 
0

Try making refference using inditect:

transform this: =RegexExtract(Sheet1!A1) to this → =RegexExtract(indirect("Sheet1!A1"))

or better this → =RegexExtract(indirect(B1)) and paste the name of range in B2.

When you'll copy the sheet to other books, you'll be able to correct value in B2 to a new sheet refference.

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.