Notice when we select “Productivity Div”, the App list Combo Box has no empty lines at the bottom. When we select “Game Div” or “Utility Div”, we have some empty slots at the bottom of the list because the referenced cell range is not filled completely.
We can solve this small issue by creating a more advanced formula that will discover the length of the current App list.
We can’t place formulas in form controls, so we will place the formula in the Name Manager and then reference the name in the form control.
A useful approach is to construct the formula in a normal worksheet cell; once it is determined that the formula is performing as desired, we will transfer the formula into the Name Manager. One of the prime benefits of this approach is the support for IntelliSense assistance.
We will use an INDEX function to determine the length of the dynamic App list.
Knowing that the list will always begin in cell N4, we will start the formula as follows (place this formula in any empty cell):
The resultant array will always begin in cell N4 but will end in different placed depending on the selected Division.
The interesting thing about this approach is that when you use an INDEX function as part of a range definition, the INDEX function returns a cell address as opposed to the contents of the discovered cell.
The array that INDEX will use is the range of possible answers that may be returned. In this case, cells N4 through N18.
The next argument is to determine how many rows to capture in this array. We want to capture as many rows as contain data. This will be determined by the MATCH function.
Since we aren’t looking for an exact match of anything, we will look for the highest possible App name that could ever occur in our list of Apps.
We will use a static “fake App” name of “zzzzzzzz”. If this were to exist and we sorted our App list, this App would always be at the bottom of the list.
We will look for this “fake App” in the list of dynamically generated Apps (N4:N18).
We want to return the location of the last item discovered, so we will use a “1” to denote a “less than” behavior.
When the MATCH returns the location of the last discovered item in the array, INDEX will turn that last discovered location into a cell address and append it to the constructed range.