I need to make an array, but when the array has a blank cell I would like to write in the cell without the whole array becoming #N/A.
I am trying to repeat this formula to the columns on the right on the same row. The formula uses array_constrain() to fill four cells in the row if the top cell is not equal to “CUSTOM”. The formula will constrain to only one cell if the top cell equals "CUSTOM". This allows me to type in the rows below when the header says "CUSTOM".
The array (row 10) starts just below the row I would like to repeat (rows 6, 7, 8, and 9). $C$12 contains how many times I would like to repeat the formula to the columns on the right.
=ARRAY_CONSTRAIN(INDIRECT(ADDRESS(6,COLUMN(),4) & ":" & ADDRESS(9, COLUMN(),4)),VALUE(IF(INDIRECT(ADDRESS(6, COLUMN(),4)) = "CUSTOM", 1, 4)), 1)
I have tried using the transpose(), SEQUENCE(), OFFSET() and REPT() functions, and cannot seem to get it to work.
Interlaced Andrew is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
when the array has a blank cell I would like to write in the cell without the whole array becoming #N/A
You cannot add a value in a cell that is part of an array formula’s result area without causing the formula to show #REF!. See Spreadsheet Value Rule.
One workaround is to insert an ‘exceptional cases’ tab where you add the values to show when the formula result is blank, and modify the formula so that it replaces blanks with values it looks up from the ‘exceptional cases’ tab. Note that this workaround will not make the formula results editable — you would need to do the edits in the ‘exceptional cases’ tab.
1