Macro to omit blank cells needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
Insert>Name>Create
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
Data>Validation>
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?
 
Back
Top