Listboxes and dynamic/spilled arrays

I am populating two listboxes with data from a spreadsheet where the data is in a spilled/dynamic array
Both ranges are on the same worksheet and accessed in the same subroutine in the same module.
Each range is a single column of data.
Both formulae are of the form =SORT(...) and both work in the worksheet; neither contains errors
Both spilled ranges are between 2 and 20 items in length.
Both listboxes are on the same form.

Please Login or Register  to view this content.

The first of those works and populates the first list box. The second fails with a "Run-time error '381': Could not set the List property. Invalid property index".

Why should the first be valid and the second not?
Have I got the code wrong (I couldn't find any examples on the web) ?

Really puzzled

M

Welcome to the forum!

Named ranges would only need the sheet codename prefix if locally named.

On the codesheet shtRespondentList, in the dropdown list of named ranges towards top left, select DateofRespondentEntriesTemp2#. If it is a dynamically named range, you likely do not have any items in the array.

We would need to see the formula for the dynamic named range with some sample data to test. Using =Sort(), I suspect that your dynamic named array would use an OffSet() to get the sorted range extents. If the dynamic array by formula is done, you may need an Offset() to get the sorted data range for List.

You can attach a short simple XLSM by clicking Go Advanced button in lower right of a reply. Click the paperclip on the reply toolbar or Manage Attachments hyperlink below the reply box to upload/attach a file.

I always use full name, it's just a habit to make things easier for me.
There are items in both list - as I said both spilled ranges contain between 2 and 20 items in use. Even if the second one fails to find any items it defaults to providing a single item.

The formula which delivers the first spilled range "Unique_Respondents#" is:
=CHOOSE(RespondentNameSortBy, UNIQUE(SORT(Form_revised[Full Name],1)),UNIQUE(SORTBY(Form_revised[Full Name],Form_revised[SortedName],1)))

The formila which delivers the second spilled range "DateofRespondentEntriesTemp2#" and fails is, in fact, the simpler:
=SORT(FILTER(Form_revised[Timestamp],Form_revised[Full Name]=$N$1,"No results"),,-1)

I have no way to test that without the actual data.

You can find the named range's address by using this sort of routine.

Please Login or Register  to view this content.

My guess is that some cell in your 2nd named range caused an error.
You can test using this method:

Please Login or Register  to view this content.