I want to clarify once again that this is the Combo Box, and not the drop-down list that is created through data verification.

Selection of values ​​for the list: =Тест!$E$3:$E$56

It is necessary to somehow eliminate the empty lines, can be at the beginning, end, or somewhere between the values.

For some reason, options like =СМЕЩ(Тест!$E$3;;;СЧЁТ(Тест!$E$3:$E$56)) do not work, removing all options from the list.

    1 answer 1

    I will clarify just in case: if there are no numeric values ​​in the range, ACCOUNT (range) = 0

    To count text values, use the ACCOUNT function. COUNTIFLES are even better - she herself is able to determine the working range and you can safely specify the full column:

     =СЧЁТЕСЛИ(Test!$E:$E;"*")

    It is necessary to somehow eliminate blank lines, can be at the beginning, end, or somewhere between the values.

    Only forming a new range on the sheet. One option is to use the array formula:

     =ИНДЕКС($E$3:$E$56;НАИМЕНЬШИЙ(ЕСЛИ($E$3:$E$56<>"";СТРОКА($E$3:$E$56)-2);СТРОКА(A1)))

    The array formula is entered by a combination of three keys: Ctrl + Shift + Enter , written in one cell and stretched in rows. If the values ​​are less than the cells with the formula, in the lower cells there will be an error #NUM! . You can get around:

     =ЕСЛИОШИБКА(формула;0)

    The formula of the named range with the definition of text values, shown below, will not take zero into account:

     =СМЕЩ(Test!$E$3;;;СЧЁТЕСЛИ(Test!$E:$E;"*"))