Data Validation List excluding blanks

Thanks:  0
Likes:  0

# Thread: Data Validation List excluding blanks

1. ## Data Validation List excluding blanks

Hi guys
I have a problem with the empty cells in the data validation list
I want the list to be without the blanks

UP

3. ## Re: Data Validation List excluding blanks

One way:

Example data (range A1:A11):

 Test Data1 Data2 Data4 Data5 Data8 Data10

Count (range C1:C2):

 Count of Non-blanks 6

Formula in C2 is:

Code:
`=COUNTIF(A2:A11,"*?")`
New table (range D1:D11):

 List Excluding Blanks Data1 Data2 Data4 Data5 Data8 Data10

Formula in D2 is:
Code:
```
=IF(ROWS(D\$2:D2)<=C\$2,INDEX(A\$2:A\$11,SMALL(IF(A\$2:A\$11<>"",ROW(A\$2:A\$11)-ROW(A\$2)+1),ROWS(D\$2:D2))),"")```
Which needs committing with Ctrl+Shift+Enter and can then be copied down.

Matty

4. ## Re: Data Validation List excluding blanks

So that my Data Validation doesn't have empty spaces at the end, I would create a dynamic Named Range as follows (notice how it uses the Count formula already in situ):

Code:
`=OFFSET(Sheet1!\$D\$2,0,0,Sheet1!\$C\$2,1)`
And I'd point the Data Validation to said Named Range.

All make sense?

Matty

5. ## Re: Data Validation List excluding blanks

thank you very much Mr. Matty
It helped much although it is difficult for me to understand the array formulas
But it works fine
Thanks again

6. ## Re: Data Validation List excluding blanks

thank you very much Mr. Matty
You're welcome.

It helped much although it is difficult for me to understand the array formulas
I find the Formula Evaluator a very useful tool for breaking down and seeing how formulas work. Also, by highlighting certain sections of formulas in the formula bar and hitting F9, you can see what each part is evaluating to.

Matty

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•