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:
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:
- A way to directly edit the contents of the filter results of
Sheet B. - 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?
Best Answer
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