Cell to restrict User Input

[midnightorion]

I have an excel sheet that does fraction calculations. But if a user doesn't enter the data in correctly, the answers will be incorrect. Is there a way to restrict a users input to meet proper conditions to help avoid improper data entry?

56' 7 3/16 <=== is the proper format

56-7-3/16 or 56 7-3/16 or 56-7 3/16 <==== wrong format

[oeldere]

your file is not clear to me.

maybe you could make a list of all data which users are allowed to imput.

then you can use that list as datavalidation (for the correct imput).

[midnightorion]

I don't think Data Validation is going to work as the users are entering in Feet and Inches figures, which are unlimted.

The user Enters Data into cell B10, and B11 and the form calculates the difference in Cell D12. So I need the users to be restricted to entering the data in the correct format of XX' XX X/X in all fields.

[rukman]

Please explain.... XX' XX X/X Data is entered by manually?

if yes....


why you are split the values entering. then merge all fields.

[midnightorion]

Feet , Inches and fractions = XX' XX X/X

Yes the data entered into fields B10 and B11 are Manual Entry. While Cell B12 is calculated automatically based on data in B10 and B11.

[Indi_Ra]

Feet , Inches and fractions = XX' XX X/X

Yes the data entered into fields B10 and B11 are Manual Entry. While Cell B12 is calculated automatically based on data in B10 and B11.

Why not introduce that date, in 3 different cells, then calculated automatically.

Instruct users to put Feet in cell, say B10, Inches in say B11 then in say B12 put that fraction and you put formula in say B13
If so user put only numbers in 2 cells and fraction in the last cell.

[nick93]

What you're saying is very hard to control in Excel. In Access you can specify data validation rules that can check for the format you describe. But in Excel your only option is VBA which I wouldn't recommend. The best thing you can do is use separate cells like Indi_Ra suggested.

[midnightorion]

Thanks guys appreciate the feedback. Could you possibly provide an example of how to implement doing your suggestion in the example excel form I've attached to the thread so I can see and try and duplicate your suggestion. Thanks again.

[JeteMc]

Here is an example of how to use Data Validation to restrict the use of a dash within the measurement. Cell B13 has the following custom data validation formula applied: =ISERR(SEARCH("-",B13))
Test by attempting to place a dash/hyphen somewhere within that cell.
Note: the message in the pop up box can be customized.

[midnightorion]

Here is an example of how to use Data Validation to restrict the use of a dash within the measurement. Cell B13 has the following custom data validation formula applied: =ISERR(SEARCH("-",B13))
Test by attempting to place a dash/hyphen somewhere within that cell.
Note: the message in the pop up box can be customized.

JeteMc

Thanks that will help alot. using that same thought process, is there a way to make it so the user has to input " ' " this into the formula as well or it'll bring up the error box?

[midnightorion]

I'm guessing I'll have to start a new thread in the VBA section to ask this question about how to apply VBA data validation to the cells. But before I do, is it possible to use VBA to restrict the user to entering in a specific manner only? meaning the user has to enter the data as let say 72' 2 3/8 and if the user does enter 72' 2" 3/8 it errors out.

[JeteMc]

...is there a way to make it so the user has to input " ' " this into the formula as well or it'll bring up the error box?

Try: =AND(ISNUMBER(SEARCH("'",B13)),ISERR(SEARCH("-",B13)))
As to the questions in post #11, I don't know much about VBA, so I can't say.
Let us know if you have any questions.

[midnightorion]

Try: =AND(ISNUMBER(SEARCH("'",B13)),ISERR(SEARCH("-",B13)))
As to the questions in post #11, I don't know much about VBA, so I can't say.
Let us know if you have any questions.

That works perfectly. Except I ran into another problem, which I don't think can be solved without using some VBA. But the suggestion you gave would be perfect if it wasn't for the fact I have users that need to enter decimal feet into the cells at times. Example: 56.25 . This will cause the error to pop up because we are missing the " ' "

I'll use your formula elsewhere though, which solves other problems I've been dealing with. Appreciate your help.

Thanks again.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.