5

We have a specific set of data that is being imported from another spreadsheet with:

=IMPORTRANGE("spreadsheetkey"; "sheetname!A8:F133")

Now we want to change that data in one of the cells within this range, for example C64.

When we do that we get the error:

Error Array result was not expanded because it would overwrite data in C64

How can we import the range and still be able to alter an arbitrary set of the imported data?

Improve this question
2
  • Why do you use IMPORTRANGE() instead of Copy & Paste?
    – Rubén ♦
    Dec 2, 2015 at 2:36
  • I have this same problem. Basically, I have a complex financial model that I want to run with different values. I don't want to copy-paste the sheet the defines the model into a bunch of other sheets because I'm still editing and adjusting it, and I want to do so in one place (plus formatting lost, etc). Are there approaches other than IMPORTRANGE() for achieving what would be considered "code reuse" in Google Sheets?
    – chadrik
    Nov 12, 2021 at 18:32

1 Answer 1

Reset to default
2

Strictly speaking, you shouldn't have it both ways: maintain a link between two data sets, yet edit one of them independently of the other. If one needs to exist independently of the other, then perhaps you should just copy-paste the values.

But here is a workaround which might suffice in your case.

  1. Put importrange command on another sheet, say Sheet2.
  2. On Sheet1, enter =Sheet2!A1 in cell A1, and copy this formula to the entire sheet (Ctrl-C, Ctrl-A, Ctrl-V)
  3. Now your Sheet1 looks just like imported range (and it will update automatically if the source spreadsheet is changed). But you can also edit any of its cells, severing the connection of that cell only to the source spreadsheet.
Improve this answer
3
  • Thank you for you feedback. The idea behind is: Use importrange quickly and precisely, and then update data accordingly. One of the reasons that this process would be used would be speed as well ( you only need to update the spreadsheets key on a preset importrange waiting to be filled and voila!) Although walking the extra mile would produce exactly the requested result, it would add extensively to the process making it cumbersome for an extensive number of spreadsheets.
    – Ioannis
    Dec 1, 2015 at 20:46
  • 1
    Sorry, I don't understand your comment. Could you express your objective in a more precise way? What data needs to be updated, why the spreadsheet key needs to be changed, and what is waiting to be filled? Most importantly, why doesn't copy-paste suffice?
    – user79865
    Dec 1, 2015 at 20:53
  • How do you do this without copying every cell by hand? Can you accomplish the same thing but do the entire sheet with one command?
    – zero_cool
    Mar 1, 2018 at 2:32

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.