Is there a way to omit blanks when validating data from a list?

I am using a list that have blank cells in it, and when using this list in
other sheet I don't want this blank cells to be displayed.

I guess I should use a macro to do this, but can´t figure it out. Any help
would be aprreciated.

Thanks in advance

Emece.-

How exactly are you trying to "validate" this data? Formulas? Built-in
functions? Please be more specific and we can come up with something for
you.

"Emece" wrote in message
...
Is there a way to omit blanks when validating data from a list?

I am using a list that have blank cells in it, and when using this list in
other sheet I don't want this blank cells to be displayed.

I guess I should use a macro to do this, but can´t figure it out. Any help
would be aprreciated.

Thanks in advance

Emece.-

Using Data - Validation - List.

In Sheet 1 I specify the name of the List that is in Sheet 2. But this list
have blank cells and I don't want them to be displayed.

Hope I am clear enough.

Thanks.

"JPW" wrote:

How exactly are you trying to "validate" this data? Formulas? Built-in
functions? Please be more specific and we can come up with something for
you.

"Emece" wrote in message
...
Is there a way to omit blanks when validating data from a list?

I am using a list that have blank cells in it, and when using this list in
other sheet I don't want this blank cells to be displayed.

I guess I should use a macro to do this, but can´t figure it out. Any help
would be aprreciated.

Thanks in advance

Emece.-

You need to fix the list, say by using sort or advanced filter and filter in
place

--
HTH

Bob Phillips

"Emece" wrote in message
...
Using Data - Validation - List.

In Sheet 1 I specify the name of the List that is in Sheet 2. But this
list
have blank cells and I don't want them to be displayed.

Hope I am clear enough.

Thanks.

"JPW" wrote:

How exactly are you trying to "validate" this data? Formulas? Built-in
functions? Please be more specific and we can come up with something for
you.

"Emece" wrote in message
...
Is there a way to omit blanks when validating data from a list?

I am using a list that have blank cells in it, and when using this
list in
other sheet I don't want this blank cells to be displayed.

I guess I should use a macro to do this, but can´t figure it out. Any
help
would be aprreciated.

Thanks in advance

Emece.-

There might be a way to do what you're asking.

Here's what I did for my example:
STEP 1:I entered the following information on Sheet1:
A1:Name
A2:One
A3:Two
A4:
A5:Three
A6:
A7:Four
A8:
A9:
A10:Five
(Note: Cells A4, A6, A8, A9 are blank)

STEP 2:
E1: =COUNTA($A$2:$A$10)
E2:=(E1-1)
Copy that formula to E3:E10

STEP 3:
F1:=INDIRECT("A"&LARGE((($A$2:$A$10)<"")*ROW($A$2 :$A$10),E1))
---Commit that array formula by pressing [Ctrl]+[Shift]+[Enter]
Copy that formula to F2:F10

STEP 4:
Create a dynamic named range
InsertNameCreate
Name: myListWithNoBlanks
Refers to: =OFFSET(Sheet1!$F$1:$F$10,,,COUNTA(Sheet1!$A$2:$A$ 10))
Click [OK]

STEP 5:
Select a cell to use validation, then
DataValidation
Allow: List
Source: =myListWithNoBlanks
Click [OK]

When you click on that cell, you should only see the non-blank items from
your list.

Does that example do what you want?
--
Regards,
Ron