Apr 28 2021 09:43 AM - edited Apr 29 2021 01:54 AM
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
Apr 29 2021 01:32 PM
Apr 30 2021 01:15 AM
May 01 2021 09:32 AM
@RoryForbes
Update for @JoeMcDaid
Hi Joe
Please see a simple workbook file attached showcasing the 2 areas where this inconsistency occurs.
=IFERROR(SORT(FILTER(tblMasterList,tblMasterList[Category]=$I$9,""),4),"")
This is the first dynamic array formula in the logic.
=IFERROR(SORT(UNIQUE(INDEX(Proto!$B$30#,,4),FALSE,FALSE)),"")
=IFERROR(SORT(UNIQUE(INDEX($B$62#,,6),FALSE,FALSE),,-1),"")
=IFERROR(UNIQUE(FILTER($AA$9:$AA$24,$AA$9:$AA$24<>"")),"")
=$AC$10#
This is the 10th dynamic array formula on the worksheet.
=IFERROR(SORT(UNIQUE(INDEX($B$62#,,2),FALSE,FALSE)),"")
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
May 01 2021 09:43 AM
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
Nov 05 2021 11:25 AM