Home |
Search |
Today's Posts |
#1
|
|||
|
|||
DATA VALIDATION
I have a field asking for a county name. There are 77 possible counties to
enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#2
|
|||
|
|||
In a convenient empty column in the workbook, preferably out of site eg a
hiddeen worksheet, enter all 77 County names (laborious I know). Name this range 'county' or whatever and in your input field go Data Validation, choose 'List' and in the dialogue box below enter =county Enter a suitable error message and that's it done. Changing the case of inputted data can be done with VBA code, but not by formatting, suggest you ask this in Programming, Regards, "barryderay" wrote in message ... I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#3
|
|||
|
|||
Thans for the quick responce. How do I name the range?
"barryderay" wrote: I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#4
|
|||
|
|||
OK I figured it out and it works. But the user can still type in whatever
they want. Now they do have the option of using the dropdown menu but they are not forced to. Is there a way to do this using the custom setting and entering a formula pointing to the named range "county'? "Alan" wrote: In a convenient empty column in the workbook, preferably out of site eg a hiddeen worksheet, enter all 77 County names (laborious I know). Name this range 'county' or whatever and in your input field go Data Validation, choose 'List' and in the dialogue box below enter =county Enter a suitable error message and that's it done. Changing the case of inputted data can be done with VBA code, but not by formatting, suggest you ask this in Programming, Regards, "barryderay" wrote in message ... I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#5
|
|||
|
|||
Highlight the whole range containing the County names, then, in the namebox,
on the left of the formula bar, (the one that says A1 if you select A1), click it and type in the name of your choice then hit enter. You need to do this with a list in Data Validation unless the list is on the same worksheet as your validated entry field, it wont take a cell reference to another sheet like Sheet3!A:A77 The advantage is that if you were using a range like A1:N87 in several formulas, by naming the range to say 'myrange' you use that instead of clumsy cell references and you don't need to worry about Absolute (dollar signs) and Relative (no dollar signs) when dragging formulas down a range. Regards, "barryderay" wrote in message ... Thans for the quick responce. How do I name the range? "barryderay" wrote: I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#6
|
|||
|
|||
If its done correctly the user can only either use the drop down list or
type in a value contained in the range 'county'. Don't have any blank cells in the range, if you do have any, unckeck 'Ignore Blank' Its all there in Help by the way, Regards, "barryderay" wrote in message ... OK I figured it out and it works. But the user can still type in whatever they want. Now they do have the option of using the dropdown menu but they are not forced to. Is there a way to do this using the custom setting and entering a formula pointing to the named range "county'? "Alan" wrote: In a convenient empty column in the workbook, preferably out of site eg a hiddeen worksheet, enter all 77 County names (laborious I know). Name this range 'county' or whatever and in your input field go Data Validation, choose 'List' and in the dialogue box below enter =county Enter a suitable error message and that's it done. Changing the case of inputted data can be done with VBA code, but not by formatting, suggest you ask this in Programming, Regards, "barryderay" wrote in message ... I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#7
|
|||
|
|||
Thanks a lot Alan! This worked great!You have been quite helpful. I appreciate your time. "Alan" wrote: If its done correctly the user can only either use the drop down list or type in a value contained in the range 'county'. Don't have any blank cells in the range, if you do have any, unckeck 'Ignore Blank' Its all there in Help by the way, Regards, "barryderay" wrote in message ... OK I figured it out and it works. But the user can still type in whatever they want. Now they do have the option of using the dropdown menu but they are not forced to. Is there a way to do this using the custom setting and entering a formula pointing to the named range "county'? "Alan" wrote: In a convenient empty column in the workbook, preferably out of site eg a hiddeen worksheet, enter all 77 County names (laborious I know). Name this range 'county' or whatever and in your input field go Data Validation, choose 'List' and in the dialogue box below enter =county Enter a suitable error message and that's it done. Changing the case of inputted data can be done with VBA code, but not by formatting, suggest you ask this in Programming, Regards, "barryderay" wrote in message ... I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
#8
|
|||
|
|||
You are most welcome,
Regards, "barryderay" wrote in message ... Thanks a lot Alan! This worked great!You have been quite helpful. I appreciate your time. "Alan" wrote: If its done correctly the user can only either use the drop down list or type in a value contained in the range 'county'. Don't have any blank cells in the range, if you do have any, unckeck 'Ignore Blank' Its all there in Help by the way, Regards, "barryderay" wrote in message ... OK I figured it out and it works. But the user can still type in whatever they want. Now they do have the option of using the dropdown menu but they are not forced to. Is there a way to do this using the custom setting and entering a formula pointing to the named range "county'? "Alan" wrote: In a convenient empty column in the workbook, preferably out of site eg a hiddeen worksheet, enter all 77 County names (laborious I know). Name this range 'county' or whatever and in your input field go Data Validation, choose 'List' and in the dialogue box below enter =county Enter a suitable error message and that's it done. Changing the case of inputted data can be done with VBA code, but not by formatting, suggest you ask this in Programming, Regards, "barryderay" wrote in message ... I have a field asking for a county name. There are 77 possible counties to enter. I want to ensure that the user enters a valid county and has it spelt correctly. Is there a way to do this? Can I have the validation point to text file with all the acceptable entries? Also is there a way to format the cell to convert anything entered into all upper case? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
DATA VALIDATION IN REVERSE #2 (FOR JULIE D.) | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Data Validation Window? | Excel Discussion (Misc queries) |