×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Force Data Entry Format in Excel

Force Data Entry Format in Excel

Force Data Entry Format in Excel

(OP)
Someone, in their infinite wisdom has created a website that connects to a database. This website has a data entry form which has a field into which three pieces of information are to be entered separated by slashes (/). The .sql code in the database then breaks up the three pieces of information and send them to three fields, each in a different table. I assume the code that separates them is dependent on the slash.
I have been tasked with creating an Excel spreadsheet containing a field to take in this same information - also in one field which, as far as I know, is to be imported directly into SQL Server. My experience with this type of thing tells me that the user cannot be depended upon to follow directions and the cells must have data validation rules set. (and they still manage to screw thing up).Is there any way to format this cell to present the user with slashes between which he will enter the three pieces of information? (I vaguely remember this being called a mask but apparently a mask is something else. What might the thing for which I am looking be called?). I've looked at custom formatting but it only seems to allow this type of formatting for numbers and dates.

RE: Force Data Entry Format in Excel

What about another approach of having three entry fields and then use vba code or excel concatenate functions to combine and output the data. That way don't have to build a lot of logic to identify whether they used a slash or not.

RE: Force Data Entry Format in Excel

It would be helpful to know what data should the user provide between the slashes (/).
Are the 'pieces' pre-defined?
For example, if you need:
Month / Day / Year
then for Month you have Jan, Feb, ..., Dec, etc.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Force Data Entry Format in Excel

(OP)
Unfortunately, I need to present the data as in the form - one field for the three pieces
The data going into the field is Building Name/Floor/Room. They are going, eventually, to tblBuilding, tblFloor, tblSpace. I believe that the code is already in place to split these three pieces of information out and feed it to the required locations. This needs to feed it into a table which is then used by existing code which would use the slashes to tell it where which piece of information is which.

RE: Force Data Entry Format in Excel

I would assume for any Building Name/Floor/Room entry, you (should) already have a list somewhere with valid Building Names, and for each Building Name - you have a list of valid Floors, and for each combination of Building Name/Floor combinations you have a list of valid Rooms. Right?
ponder

BTW - it is really bad process (in my opinion) asking user to provide this data as '/' delimited data, and then have to split it into any useful data in DB.
banghead

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Force Data Entry Format in Excel

(OP)
I don't have any of that information - the user is supplying it.

I agree. It is bad form but that's the way I've been instructed to do it. I've had this type of nonsense happen before.

RE: Force Data Entry Format in Excel

Then I would go with sxschech's suggestion:



Users can type whatever they want in columns A, B, and C (as long as they do not type any slashes), and you concatenate the data in column D

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Force Data Entry Format in Excel

(OP)
Thanks. I guess the best to suggest that they do it that way. Thank you.

RE: Force Data Entry Format in Excel

You may enhance the input by informing user about Slashes (in columns A, B, or C) with a little VBA:

CODE

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Column
    Case 1, 2, 3
        If InStr(Target, "/") Then
            MsgBox "No Slashes Allowed"
        End If
    Case Else
        'Who cares...
End Select

End Sub 

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

RE: Force Data Entry Format in Excel

An alternative method could be implementation of data validation:
1. allowed: custom formula,
2. entry message: describe what is allowed, it will be displayed as a moveable tooltip when a cell with DV is active
3. error message: message and its style if wrong data.

Assuming that DV refers to data in A1, formula in DV:
1. if empty fields are allowed, as "/bbb/ccc":
=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,TRUE) 
2. if empty fields are not allowed:
=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,FIND("/",A1)>1,FIND("/",A1,FIND("/",A1)+1)>FIND("/",A1)+1,LEN(A1)>FIND("/",A1,FIND("/",A1)+1)) 

The DV can be set in other cells with copy-paste or paste special option.

The drawbacks of DV:
- user can leave empty cell,
- user can bypass DV and paste any contents.

combo

RE: Force Data Entry Format in Excel

Actually, formula in case (2) above can be simplified and use wildcards, so will be easier to manage:
=AND(LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))=2,ISNUMBER(MATCH("*?/*?/?*",A1,0))) 

The first condition in AND requires exactly two slashes, the second finds the pattern in entry string.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close