Why Are Checkboxes Breaking My FILTER?

Question

I am filtering data from another sheet. The filtered range is chosen based on a dropdown. The filtered data is mostly true/false values. I want to display these data points as checkboxes. When I highlight the filtered range containing the true/false values, and Insert > Checkboxes, the filter formula stops pulling the data in and give me the error #REF! "The array was not expanded because it would overwrite data in cell ___"

I'm suspecting that it might have to do with the fact that the size of the filtered range changes when I change the value in the dropdown, but I'm not sure.

See an example of this problem here. https://docs.google.com/spreadsheets/d/1TYjZmlJMMgmMPVRyQwHQHWP-fHFAF8GjggCfvDyzCdc

I've tried deleting the checkbox at the specific cell mentioned in the error, but then it just changes to another one. The original data is also using checkboxes.

Answer 1

The issue here is that inserted checkboxes default to a value of FALSE, so when you highlight the filtered range and then insert checkboxes these FALSEs overwrite the FILTER range and prevent the array it is generating being expanded. Instead, select the FILTER range and then use the checkbox option in Data validation instead - this will enable checkboxes but will let you see the underlying TRUE/FALSE result from the formula.

Answer 2

I've tried deleting the checkbox at the specific cell mentioned in the error, but then it just changes to another one.

This is what it is trying to return:

This is what is in the way:

When an array formula needs to write data in a cell that already contains data, you will get a #REF! error that reads: "Array result was not expanded because it would overwrite data in [cell address]"

The error lists the first cell it encountered with data, which in your case was H2. But, as you discovered, clearing data from that cell may eliminate the error if it encounters more data in its way it will list the next cell.

The array you are trying to return wants to fill the range G2:J5

but you have data in the range H2:J5

In order to avoid that error you would need to clear data from each of the cells in the range H2:J5 i.e. { H2, I2, J2, H3, I3, J4, H4, I4, J4, H5, I5, J5 }

Show Checkboxes (vs. TRUE/FALSE)

Your formula returns TRUE/FALSE values. If you want to return checkboxes here are two options.

Option 1: Replacement Characters

^{**   This is my recommended option   **}
You can SUBSTITUTE characters for your TRUE/FALSE values. These characters will not be dynamic checkboxes. This should not matter since the TRUE/FALSE state of these cells cannot be individually changed. All the cells' states are controlled by the array formula in cell G2. You could use any character including many emojis.

Examples:   🗹   ✓   ✔   🗸   ✅   ❌   x   ☐   -   etc.

These characters can be used in formulas just like you would regular checkboxes =IF(A1="🗹",TRUE,FALSE)

Here is an example formula using TRUE = ✔ and FALSE = -

={ B1:E1; ARRAYFORMULA(
     SUBSTITUTE(SUBSTITUTE(FILTER(B2:E, B2:B=G1),
       TRUE,"✔"),FALSE,"-")) 
 }

Option 2: Helper Columns

^{**   This is NOT recommended  **}
If you really want to use checkboxes, you could use helper columns to determine their state.

1. Put your original =FILTER(B2:E, B2:B=G1) in another column in the same sheet or a different one.
2. Put checkboxes in each cell in the range H2:G2 and as many rows as will cover the most rows of results that could be returned by your formula.
   
    
3. Put an ARRAYFORMULA in G3 to return the "Class" from your first helper column =ARRAYFORMULA(L3:L10)
   
    
4. Link the value in H3 to the value M3 (helper table Row1, Col2), and then copy that formula across all the checkboxes
   
    
5. [Optionally] Create a Conditional Formatting rule for the range of checkboxes based on IF the value in the "Class" column ="" (is empty) that changes the font to match the background color (white in my example) hiding them from view. * Sheets will allow you to toggle hidden checkboxes, overwriting the formula, by mouse-clicking, but will warn you first. Sheets will not toggle visible checkboxes with formulas via mouse-click