1

Brief: The results of a filter are not mutable. I cannot directly add/edit rows to the cells in and around a filter result due to a #REF Error: not expanded because it would overwrite data error.

I have two tabs of Sheets. I'm referencing a range of columns from Sheet A onto Sheet B by using the filter function.

Here are two images illustrating the above statement:

Sheet A, the original sheet

Sheet B showing <code>filter</code> function and results

The functionality I'm going for, as shown in Image A/Sheet A, is being able to change the dropdown value in the Category column to Bug, and having certain values from that row be transferred over to the Sheet B.

As shown in Image B/Sheet B, I can add columns to the right, due to it not interacting with the filter results.

My problem is in trying to find these two things:

  1. A way to directly edit the contents of the filter results of Sheet B.
  2. A way to freely & manually add rows around the filter results, and continuously have filter results be transferred over to this sheet without issue.

Attempting to do 1 or 2 will result in some or all of the data to disappear or a #REF error to occur.

How can I achieve the desired results with or without filter?

0

On Google Sheets formulas results aren't mutable. One alternative is to replace the formula by it's results. This can be done "manually" with the help of copy > paste as values. Another alternative is to use a macro or script either.

The way to turn update the filtered results is to remove the filter, put back the formula, then reapply the filter.

A third alternative is to use use a Google Sheets add-on, as the one that I made that is called Spreadsheet Freezer, this do the copy - paste as values / put again the formula with few clicks. The remove/reapply a filter isn't included on the current version.

Putting again the formula in the same place, implies to remove the values and those that were edited could be lost. To keep the edited values, before placing the formula you should copy them so some place then adding them again. This also could be automated by using Google Apps Script.

It's worth to note that Google Apps Script can be used to filter the data.

Maybe all the above makes to rethink about your solution either applied a "spreadsheet thinking" of by looking for another app like Google App Maker.

Related

0

The "functionality" you seek simply does not exist in Google Sheets. If you need to control the FILTER output from spreadsheetB you are supposed to create a reference from spreadsheetA, eg. push the value of dropdown selection from spreadsheetB to spreadsheetA which will control the FILTER in spreadsheetA and send the result in spreadsheetB.

The other workaround would be to copy the output of your FILTER formula and press
CTRL + SHIFT + V

  • Would you be able to provide a bit more detail on how to carry out your example? SpreadsheetB does not have the dropdown available, and SpreadsheetA does not have the filter. The copy/paste is a workaround that does not satisfy my requirement 2, which is having results from SpreadsheetA continuously pushing rows to B, since it removes the filter in B. – Homer May 18 at 15:48
  • As already mentioned, what you are trying to accomplish is not possible. Formulas are not mutable. the only workarounds are either replacing formula output with its values or redo the logic and create a loop-reference – user0 May 19 at 20:59

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.