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?

  • Why do you use IMPORTRANGE() instead of Copy & Paste? – Rubén Dec 2 '15 at 2:36
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.
  • 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 '15 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 '15 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 '18 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.