Data Validation with Blank Cells

[Willmonbo]

Hi All,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have a data validation range that includes cells that have no data yet and it shows up on the drop down list as empty space, is there a way to prevent it from appearing in the drop down list until something is entered in the cell?
<o></o>
Thanks in advace

 

[doofusboy]

Enter this formula as the 'Source' for you data validation list:

=OFFSET(MySource,0,0,COUNTA(MySource),1)

where 'MySource' = a range name given to the range of possible values for the data validation list, including the blank cells [of course you can use any range name you chose instead of MySource]

 

[Willmonbo]

Works Like a charm, thanks much.

 

[Willmonbo]

I greatly appreciate your help, I am just curious if there is a way to write the formula so that even if one or a couple of cells remain blank between text groups inside the data validation range, it should still the second text group and include it in the drop down list, right now, it looks like if one cell is kept blank it doesn’t pick any additional text when they are entered below the blank cell, or some do appear but not all entries.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Once again, thanks much.

 

[doofusboy]

Sorry, solution I provided will only work for contiguous entries in the named range source. [guess I should have mentioned that]