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

Hi, this is my first post, and I've been pointed here by a MS365 support ambassador whose help I have exhausted.  Edited to include JoeMcDaid Excel Program Manager as this seems to be your bag, Joe.  Thanks in advance.

I am building a sales tool in Excel that asks representatives a series of questions about a long list of products that are broken down into 3 filter questions.  It's essentially a cascade.

Each product has a category, an application and up to 5 discrete functions. 

Q1 chose category, Q2 choose an application (filtered by category), Q3 choose function(s) filtered by application.

I am using a series of UNIQUE() and FILTER() formulae which work perfectly when used to populate data validation lists using Named Ranges on the face of the worksheet.  No problem there.

However, when I use these same Named Ranges to populate ListFillRange in my ListBox controls on either a worksheet or a userform, the lists become unstable and return only partial lists to the control.  Sometimes they work OK other times they bomb fairly routinely.

This has resulted in 2 sessions out of several dozen where I get an Out of Memory message.  Am I overloading the PC memory with several dynamic array formulae in the controls - remember they operate fine every time on a native worksheet using # spill named ranges.  The problem only occurs with listbox controls on userforms or on the face of worksheets.

Any ideas on things to check, avoid, restrictions etc?

This is the 3 dynamic array formulae working perfectly on the face of the worksheet.

If I use the same dynamic array formulae to populate listbox controls then they fail to display all the values. See next image.

Here the ListBox for selection of Application has the same ListFillRange as the Data Validation on the earlier worksheet.  Sometimes it works, here only 3 of the values in the array are displayed - when there should be 5 per the first image.

This image shows a further restriction in the displayed list values when same RowSource this time is used for the ListBox in the userform.  But the Userform below in the VBE shows with the populated list from the dynamic array named range.

I fear I have spent too long looking at this now and there may be an answer somewhere obvious I am looking straight at.  Has anyone seen these symptoms before and come up with a resolution.

If no quick answer I am happy to post the file but will need to redact some company confidential information.

Many thanks in advance

Rory

Windows10 Pro 64 bit 8Gb RAM

Microsoft Excel for Microsoft 365 MSO (16.0.13901.20436) 64 bit