Using COUNTA to Eliminate Blanks in a Drop Down List?

First, here is the formula I'm using:

=OFFSET(Table60s1, 1, MATCH(CALLS!B3, dd_Formations, 0)-1, COUNTA(OFFSET(Table60s1, 1, MATCH(CALLS!B3, dd_Formations, 0)-1, 10000, 1)), 1)

The name of this formula is Plays60s1. I currently have it in a dependent drop down list. I have many items in many different tables. Right now this randomly goes down 303 rows, when in fact Table60s1 is only 33 rows deep. I do have other sections within the different tables that are shorter and some that are much deeper, hence the 10000, another reason I want to eliminate the blanks at the end of the list.. How do I fix this to eliminate the blanks correctly within the scope of Table60s1 instead of encroaching on 3 other tables?

I use this formula as well to bring up the drop down list, named dd_Plays. Here is a shortened version

=IF(CALLS!D3="",SeriesName,IF(CALLS!D3="60's 1 High",Plays60s1,""))

The formula continues to use all of the different table and formula name to create a menu of sorts inside one cell for my drop down lists.

Suggest you post a sample workbook so we can see how the data is laid out.


Regards, TMS

Generally speaking, it is more practical to put the tables side by side, rather than underneath one another. You can then create Dynamic Named Ranges based on one of the columns; ideally, a fully populated column. In order to create the Dynamic Named Range(s), the rows under the tables must be blank.

Alternatively ... again, with tables side by side, and separated by at least one blank column, you could convert the tables to Structured Tables. Excel will manage the size of the tables (in terms of rows) and you can define Named Ranges (equivalent to existing Named Ranges, I guess) to refer to the Structured Tables and/or columns within the Structured Tables.


Regards, TMS

You're welcome.