Data Validation

[DejaVu]

I'm wanting to add some data validation to a worksheet that pulls
information from several other worksheets that many other users
frequently use.

For example, each user enters info into their own worksheet. John Q.
Doe enters his information, Billy T. Bob enters his information, etc...

At month end, I run a "Summary" report that will pull all of their
information into one master report.

I recently started to run into problems because each user should enter
his/her initials (ex. JQD or BTB), but instead they are entering their
full name. When my "Summary" report is run, it looks for the initials,
and not the name.

I'm wanting to know the best way to do some data validation. Either:
1. Set up some validation for each users worksheet, or
2. Set up some validation for my master "Summary"



On my summary, each users initials are in column A. It looks like
this
ABC
SST
BES
SHT
John Q. Doe
FRB
Billy T. Bob
Etc...

If I go with validation for my master "Summary", I'm wanting some
validation that will only accept 3 alpha characters in Column A.



Any help is greatly appreciated.

Thanks in advance!!


DejaVu

 

[bhofsetz]

Why not eliminate the problem in the first place and have the workboo
into which they are entering data only accept initials (3 characters
in the cell into which they are supposed to be entering their initials
If they don't enter their initials it could remind them that they ar
only supposed to enter their initials and have them correct the error.
Less work for you in the end

 

[DejaVu]

bhofsetz, I was going to go with that approach, but I question ho
effective it will be. Each user is supposed to get the spreadshee
from the network each time they fill it out, then submit it to me. Th
problem is, some users save a copy of the spreadsheet to their local PC
then just modify that one, save a copy and send that to me. It would b
very difficult to add the data validation to the user's worksheet, but
agree that it would be the most effective in fixing the problem.

If I can push it through, I will modify their worksheet and force the
to use that one. As long as they accept the new version, then it woul
be fine for them to have that saved to their local PC. I do not reall
see any other way around this issue, do you?


Thanks again bhofsetz!


DejaV

 

[bhofsetz]

I suppose that you could do a data Validation on your Summary sheet by
having the macro check the values of cells in column A and see if their
length is greater than 3.
If they are longer than 3 characters you could extract the initials by
taking the first character, searching for each space and take the
character immediately following the space.
Then have the new string with the initials reasigned to the cell where
their full name was.
This approach would assume they have entered their name as John Q. Doe,
etc... and not used any prefixes (Mr., Mrs., Dr., etc...).

Still I think having the original worksheet force the user to enter
initials would be your best solution.
But if you implement the Validation steps above on your Summary sheet
now even if you can get the users to use the updated worksheet later it
shouldn't matter if you have the Validation step in your Summary sheet
or not.

If you need help figuring code to extract their initials I can help you
with that unless someone else alreay has a macro to do that.

HTH

 

[DejaVu]

I could create the validation for my "Summary" sheet, but I do not thin
that it is the best course of action.
The easiest and BEST way (suprising to say the easiest way is the bes
way) to fix this would be to have all my users input their names int
an updated worksheet that has the data validation. This may be trick
to get implemented, but it is without a doubt the best way to handl
it.

Thanks for the advice and feedback bhofsetz. It is much appreciated!
Its nice to hear other viewpoints on the problems!


DejaV