Automatically expand range of Excel array formula (without macro)

Question

Problem

How can I automatically add rows to an array formula as I manually add rows to a table? Similar functionality seems to be offered by Google Docs at https://support.google.com/docs/answer/3093275?rd=1, and is adapted for Excel with a macro at http://www.wilmott.com/messageview.cfm?catid=10&threadid=62734. However, using this macro requires enabling Microsoft Scripting Runtime, which I'd rather not do, and would also be difficult for the end-user to maintain. I'm running Excel 2013.

I am creating this worksheet for an end-user who may not be knowledgeable about VBA or array formulas, but will need to add items to a Validation table (described below). As the user adds table rows, my data validation range and its array formula are not automatically increased in length, and it's probable that the data validation will no longer show all acceptable values.

Setup for Filtered Data Validation

You can view my stripped-down worksheet with macros disabled here. Data validation on the Process column in the Main table will only show values that are currently showing in the Testing Process column in the Validation table. Slicers are included for ease of filtering.

Using help from http://www.contextures.com/xlDataVal02.html and some other sources, I have created a table named Main_HIGHLIGHT with data validation that only allows the visible/filtered values of a column in a second table named Validation.

The second table has three relevant columns, Visible, Category, and Testing Process. The table is filtered with a slicer on the Category column and the data validation returns values from the Testing Process column. A three-step process is used to prevent filtered values from appearing in the data validation:

1. Cells in the Visible column shows a blank if the table row is filtered out, and show the value of Testing Process if it's not filtered out. Its formula is:
   =IF(AGGREGATE(3, 5,[@[Testing Process]])>0,[@[Testing Process]],"")

2. An array formula directly to the left of the table, but not a part of the table, takes the range from Visible and sorts it so that all the blank cells are at the bottom of the range, and all the cells with a value are at the top. It fills a range with width 1 and height equal to the number of entries in the table. This range is given the Defined Name Visible_Tests_with_filtered_removed. The formula, entered with Ctrl-Shift-Enter as usual, is:

   =INDEX(Validation[Visible],
           SMALL(
               IF( Validation[Visible]<>"",
                       ROW(INDIRECT("$A$1:$A$"&COUNTA(Validation[Category]))),
                       ""
               ),
               ROW(INDIRECT("A1:A"&COUNTA(Validation[Category])))
           )
   )
   

3. A Defined Name, Visible_Tests_with_blanks_removed, is created that includes only the values from Visible_Tests_with_filtered_removed, not any blanks or errors. Its formula is: =OFFSET(PPRNT!$A$34,0,0,MATCH("*",Visible_Tests_with_filtered_removed,-1),1)

Potential Solutions

Ideally I'd like to add the array formula to the Validation table, since that would automatically copy the array formula to any new row as it was added. When I try doing this, however, I get the error that "Multi-cell array formulas are not allowed in tables."

Alternatively, perhaps I could put this entire range into another Defined Name like Visible_Tests_with_blanks_removed, whose values are not actually located in cells on the worksheet. I don't know

If all else fails, I could use the macro I linked above, but it seems to me it shouldn't be this hard and I would probably just include instructions for expanding the Array formula in the HowTo tab.

Answer 1

Thanks @OldUgly for a bump in the right direction.

This three-step process creates the proper list for data validation, and it's part of the table so it updates automatically. Note that the CSE formula I used in my original question was multi-cell (select a bunch of cells, then enter the formula and hit Ctrl-Shift-Enter), but those aren't allowed in tables so this solution uses a single-cell array formula, which is automatically copied to every cell in the column in the table.

1. Create a Visible column on the table to determine which rows are currently hidden. This is a normal formula, not a CSE formula.
   =IF(AGGREGATE(3, 5,[@[Testing Process]])>0,TRUE,FALSE)

2. Add a Filtered List column to the table, and enter this single-cell CSE formula (which should get automatically copied to the rest of the cells in the table, just like any other table formula). If you press Enter instead of Ctrl-Shift-Enter, you'll get the #NUM! error for all but the first cell.

           =INDEX([Testing Process],
           SMALL(
               IF([Visible], ROW([Testing Process])-ROW(Validation[[#Headers],[Testing Process]]), ""),
               ROW([@[Testing Process]])-ROW(Validation[[#Headers],[Testing Process]])
           )
   )
   

3. Create a Defined Name Testing_Processes_for_Data_Validation (Formulas->Define Name) with the formula =OFFSET(Validation[[#Headers],[Filtered List]], 1, 0, MATCH("*",Validation[Filtered List],-1), 1) so that the Data Validation doesn't have a bunch of #NUM! errors at the end of it.

   When activating Data Validation on a cell, set Allow to be "List" and Source to be =Testing_Processes_for_Data_Validation.

And that's it! This creates an automatically-expanding dynamic list based on the filtered column of a table, and removes blanks and errors from that list.