Data Validation help

[mayanksaini11]

I want to use data validation in 'client1' file for the data range available in "Mater_Time_Sheet"(Q6:Q55) . The file is attached for your reference. I have tried different ways available on internet but it hasn't worked. Also earlier the Master file was locked, now it has been opened but still the problem exists.
The range in "Mater_Time_Sheet"(Q6:Q55) shows the name of employees also some cells are vacant for the employees who will join in future. So I don't want the blanks to be reflected in the data validation in client file. The data validation will be done client file in C2 cell.

[Bernie Deitrick]

When you open this, make sure that you change the links to your "Master_Time_Sheet" - it may be looking for mine. If you store them in the same folder, the links may work.... I have assumed that the list of names always has no blanks within the values, only at the bottom....

Client with Name Validation.xlsx

[mayanksaini11]

Thank You Bernie. I worked well as I wanted
Can you also get the data validation on with auto complete? I have searched on google, I could find for excel 2010 but not for 2007.

[Bernie Deitrick]

For data valaidation with Auto-complete, the easiest thing to do is to add a userform with a textbox and a listbox to your project, then use event code like this in the worksheet (Chage "RangeWithList" to the cells addresses where you want autocomplete with validataion.) Change the size of the userform and listbox to show enough of the list of allowed values, and make it pretty

Please Login or Register  to view this content.

And in the userform's codemodule, use this code: When you select the cell, your will see the userform, and can begin typing in the TextBox to reduce the list entries in the listbox - if you get to a few, you can click the entry in the listbox; if you get to just one, it is entered in the cell without having to click the listbox at all.

Please Login or Register  to view this content.

[mayanksaini11]

Thanks Bernie for your time and your quick replies
Well I am not familiar with macros, even copy pasting and running. So it didn't work for me.
Rather I have found a simple solution for autocomplete is to put the data validation cell just below the ranged list. My question to you now is how I can remove the blanks from the data validation drop down list? I am using same formula of yours in name manager as =OFFSET(Initial!$C$1,0,0,COUNTIF(Initial!$C1:$C50,"?*"),1) for Data validation in 'Initial!C51'.

[Bernie Deitrick]

You can either use that same formula in the list option, or use

=NameOfRange