Using dynamic arrays to populate named ranges for ListBox Controls is unstable - user error?.

RoryForbes 

Hi JoeMcDaid 

Pulling my hair out now with this forum platform.  I've submitted my reply after a lot of effort which is a lengthy explainer about the workbook sample and it's now disappeared (my reply with workbook) from the thread.

I'm attaching the workbook again but I don't have time right now to re-write the explainer - I wish I'd done it in Word rather than type direct into the forum rich text editor; but ah well, lesson learned.

Here is the workbook.  It's a simple cascade from q1 to q3 but I have to put the data through lots of gyrations - I have counted 11 dynamic array formulae on the face of the Proto worksheet before we start thinking about these being repeated in Named Ranges to be recycled into ListBox controls.

Take a look and ask me any questions.

Much appreciated.

Rory

RoryForbes 
Update for JoeMcDaid 

Hi Joe

Please see a simple workbook file attached showcasing the 2 areas where this inconsistency occurs.

1. Start on the sheet "Proto" and use the Data Validation dropdown in cell Proto!I9 to choose one of 6 categories of product.  This is populated from a simple, static named range called "Category" (see sheet "Data model components").
2. The Data Validation in cell Proto!I9 uses '=$I$11#' as the Source for the list validation.
   
3. Choose 'Bakery' from cell Proto!I9.
4. Note that my first dynamic array formula sits in cell Proto!B30 and this spills all the products from the table 'tblMasterList' that have the category of 'Bakery'.  This filters (by Category - cell B28) and sorts (by Application - column 4 in the array) all table rows In this dataset there are 6 rows returned. 

=IFERROR(SORT(FILTER(tblMasterList,tblMasterList[Category]=$I$9,""),4),"")​

This is the first dynamic array formula in the logic.

• Note that my second dynamic array formula sits in cell Proto!K11 and this spills all the unique values found in column 4 of the first dynamic array formula into a new array.

=IFERROR(SORT(UNIQUE(INDEX(Proto!$B$30#,,4),FALSE,FALSE)),"")​

• The Data Validation in cell Proto!K9 uses a named range as a source '=DynamicApplication'.  This named range Refers to '=Proto!$K$11#' as the source of data for the List.
• Note the third dynamic array formula in cell Proto!B62.  This filters the tblMasterList once more but this time by the value of the selected Application.  This returns 3 rows from tblMasterList.
• Choose 'Tortillas' from cell Proto!K11.
•  
• Note there are now 5 more dynamic array formulae in cells Proto!AF2, AG2, AH2, AI2 and AJ2.  These allow me to store the maximum of 5 function values that each combination of product/category/application may hold.  These formulae, example of cell AF2 below, allow me to sort the unique functions as returned by the dataset returned from the formula in cell Proto!B62.

=IFERROR(SORT(UNIQUE(INDEX($B$62#,,6),FALSE,FALSE),,-1),"")​

• I then squeeze the values stored by the 5 array formula through some 6 steps of text manipulation from cell Proto!Z1 finishing at cell Proto!AC10 which gives me a unique list of the filtered functions available for the selected product/category/application combinations that I have just cascaded through.
• The ninth (9th) dynamic array formula now gives me the source for my 3rd Data Validation List in cell Proto!M9. 

=IFERROR(UNIQUE(FILTER($AA$9:$AA$24,$AA$9:$AA$24<>"")),"")​

• At cell Proto!M11 I then copy across the dynamic formula from cell Proto!AC10 as follows

=$AC$10#​

This is the 10th dynamic array formula on the worksheet.

• The 11th dynamic array formula on the sheet is at cell Proto!M20 where I then present all the unique products that are returned by the dynamic array formula at cell Proto!B62 using this next formula.

=IFERROR(SORT(UNIQUE(INDEX($B$62#,,2),FALSE,FALSE)),"")​

• That is all the dynamic array formulae.  You can now see that the 3 Data Validation lists in cells Proto!I9, K9 and M9 all properly provide the filtered and cascaded list of values through my questions 1,2 and 3.
• So far so good.  This all works perfectly using the data validation lists on the face of the worksheet, if a little cumbersome to get to the cascaded filtered lists, but it works.
• Now when I try to add in some UI/UX improvements by introducing ListBox controls on a new worksheet 'Clean' and on 'UserForm1' then the behaviour of the 3 filtered lists starts to get unreliable.
• On sheet 'Clean' I have 4 listbox controls.  They all have named ranges as their ListFillRange properties.  The 3 'Dynamic...' named ranges use simple spill cells as the Refers to value.
  ChooseCategory = 'Category' Refers to simple static named range
  ListBox1 = 'DynamicApplication' Refers to: '=Proto!$K$11#'
  ListBox2 = 'DynamicFunctionality' Refers to: '=Proto!$AC$10#'
  ListBox3 = 'DynamicProduct' Refers to: '=Proto!$M$20#'
• On 'UserForm1' I have similarly 4 listbox controls.  They all have named ranges as their RowSource property value as follows:
  ListBox1 = 'Category'
  ListBox2 = 'Dynamic Application'
  ListBox3 = 'DynamicFunctionality'
  ListBox4 = '=Proto!M20#' - note as a test to see if it made a difference compared with 'DynamicProduct'.
• So overall I have well over 11 dynamic array formulae which function fine using DataValidation lists but start behaving badly at the introduction of ListBox controls.  I am either overusing them or there is a problem in the way that Excel assigns memory to these new formula when used with controls - maybe.

Over to you Joe, to take a look.  The workbook is as simple as I can make it to show all the work that is going on through each step through the cascade.

Many thanks

Rory