I am using fantastic Arrayformula to populate a grid in a Google Sheet:

enter image description here

The following is being used in each column: =arrayformula(if(Z2:Z <> "","■",""))

Now there are some cases where the marking "square" could be inserted manually, however this will break the Arrayformula as it cannot overwrite.

How could I achieve this?

  • you want to achieve what? not breaking arayformula after you break it manually?
    – user0
    Jun 13 '18 at 14:05
  • 1
    what about dropping that arrayformula and using one-cell-solution: =if($Z2 <> "","■","")
    – user0
    Jun 13 '18 at 14:13
  • What about pasting as values then make the manual changes?
    – Rubén
    Jun 13 '18 at 14:48
  • @Rubén could you expand your solution?
    – Riccardo
    Jun 13 '18 at 14:51

Replace the formula by its results by using copy & "paste as values".

  1. Select the area of the fantastic ArrayFormula results.
  2. Click on Edit > Copy
  3. Click on Edit > Paste special > Paste values only

Now you could add edit the any cell in the range without losing the values originally retrieved by the fantastic ArrayFormula.

Related Q&A

  • 1
    the above answer works. the problem though is that the new information will be static. if the source spreadsheet is edited, the destination information will not be updated.
    – random guy
    Apr 1 '19 at 19:42

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.