How can I allow results from a Google Sheets 'filter' action to be mutable?

Question

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:

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?

Answer 1

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

• Recalculate google sheet on demand:

Answer 2

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

Answer 3

Just to make sure I understand: you have a list of editable fields on Tab A and want those fields to autopopulate on Tab B, which you're importing using the filter function. You would also like to be able to add additional fields on Tab A and have them transfer over in Tab B without generating a #REF error - is that right? If so I have a couple ideas:

1. I use the filter formula but usually limit it to just the whatever column has a unique identifier (you could just create a new column in column A and add a unique number to each cell in that row (1, then 2 then 3 etc); then keep all the usual information you currently have on Tab A, but starting in the next column. Then use the filter formula on the next page to pull a list with those numbers.

To copy the remaining values that you have on Tab 1 over to Tab B I always use an index match formula which allows you to pull any cell value over as long as 1 cell perfectly matches a corresponding cell on the other tab (which you've already done using the filter to match columns A on each tab). This means you're still getting all the same values as you would if you had a Filter spanning multiple columns, but you have the flexibility to add new information/columns wherever you need: if you insert a column (on Tab. B) before a column with an IndexMatch it will adjust the index formula accordingly so you won't have to redo the formulas, and now you have a new column with which you can do whatever you want.

2. In terms of number two I have an option but it only works if your list will stay more or less the same (otherwise the cell you input won't align correctly when the order of your list changes). Assuming the list will stay in the same order (ie. Column A always goes 1, 2, 3 / there's no risk of it switching to 1,3,2 etc) then try the following: Assuming your 'Bug' field is in column D, on Tabs A an B, use the index match formula described above to have the edits you make on Tab A flow over to Tab B, but that won't allow the inverse (edits on Tab B flow to Tab A) so my suggestion is: Create Column E, and call it "EDITABLE BUG" or something of that nature which you can type in that call what you'd like it to display. Then create an "EDITABLE BIG" column on TAB A and use the same indexmatch formula to pull over the 'desired' input. Finally create another column (let's call it column I) with an IF statement (still on TAB A) something like: =IF('EDITABLE BUG COLUMN' >0, ''EDITABLE BUG COLUMN','BUG COLUMN' - basically if there's an input on Tab A (which is pulling from your manual override in Tab B) then use that, if it's blank then go with whatever you already had in the original bug column.

Lastly make sure your corresponding 'bug' column on TAB B is referencing the column L, the one from Tab A which had the "IF/THEN" formula built in.

Really hope that helps!

Answer 4

Use Filter Views instead. This will allow you to directly edit data.