Ignore blank thread

[raj]

Hi All,

I just learnt the option "Ignore Blank" in Data validation

As I understood, this option eliminates blank entries which are at the
end of list.

But my list has blank entries in between. Please some one suggest me
how can I eleminate these blank cells which appear at the middle of my
list.

Thanks in advance for your valuable suggestion

Raj

 

[Debra Dalgleish]

The 'Ignore Blank' setting can't eliminate blanks in the middle of your
list. Why do you need them? Can you sort the list so all the blanks
appear at the end of the list?

 

[Dave Peterson]

I think that this'll eliminate all the blanks:

I put my list on Sheet1!A1:A10 and used sheet2!B1:b10 as a helper column.

I copied (with minor adjustments) the formula developed by Peo Sjoblom from
Debra Dalgleish's site:

http://www.contextures.com/xlDataVal03.html

I put this in B1
=IF(ISNUMBER(SMALL(IF($A$1:$A$10="","",ROW($A$1:$A$10)),ROW(1:1))),
INDIRECT("A"&SMALL(IF($A$1:$A$10="","",ROW($A$1:$A$10)),ROW(1:1))),"")
(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Then define a range name:
Insert|Name|Define:
I called it myList
and in the refers to box, I put:
=OFFSET(Sheet1!$B$1,0,0,MAX(ROW(Sheet1!$B$1:$B$10)*(Sheet1!$B$1:$B$10<>"")),1)

Then in the data|validation cell, I just allowed a List and made the source
"=myList" (w/o the quotes).

 

[Dave Peterson]

I forgot to say to drag that giant array formula down from B1 to B10.