Mayanwolfe
New Member
- Joined
- Jun 5, 2013
- Messages
- 27
I have a dropdown in which a state can be selected. A second dropdown then retrieves and displays a list of the counties in that particular state that the user can choose from.
The available county list is retrieved using an array formula:
{=IFERROR(INDEX(RatingTables!$MF$8:$MF$3195, SMALL(IF(LandingPage!$G$20=RatingTables!$ME$8:$ME$3195, ROW(RatingTables!$ME$8:$ME$3195)-MIN(ROW(RatingTables!$ME$8:$ME$3195))+1, ""), ROW(B1))),"")}
and there are a variable number of counties in each state, so I have dragged the formula quite far down the column.
The problem is that because of this, my county dropdown has quite a few spaces at the end of its selection list (depending on the state). I've attemped to remove them using a dynamic table (the available counties populate in column R):
=OFFSET(LandingPage!$R$1,1,,COUNTA(LandingPage!$R:$R)-1)
but it still includes the spaces at the end of the dropbox scroll list.
Does anyone know how I can get rid of those pesky spaces? Thanks so much!
The available county list is retrieved using an array formula:
{=IFERROR(INDEX(RatingTables!$MF$8:$MF$3195, SMALL(IF(LandingPage!$G$20=RatingTables!$ME$8:$ME$3195, ROW(RatingTables!$ME$8:$ME$3195)-MIN(ROW(RatingTables!$ME$8:$ME$3195))+1, ""), ROW(B1))),"")}
and there are a variable number of counties in each state, so I have dragged the formula quite far down the column.
The problem is that because of this, my county dropdown has quite a few spaces at the end of its selection list (depending on the state). I've attemped to remove them using a dynamic table (the available counties populate in column R):
=OFFSET(LandingPage!$R$1,1,,COUNTA(LandingPage!$R:$R)-1)
but it still includes the spaces at the end of the dropbox scroll list.
Does anyone know how I can get rid of those pesky spaces? Thanks so much!