Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Hi, In cells D4:D13 I ask the users of this Excel sheet to type in a thickness of material. This is a decimal followed by three digits. Example: .250 But if they type in the inch mark after the thickness, it messes up the formula I have in related cells E4:E13. For example: In E4 I have =(IF(D4<=0.225,Pricing!C2,IF((D4=0.301),Pricing!C 37,Pricing!C19))) All is well if the user types in something like .250, but if they type in .250" my formula does not work. I can't use drop down menus or Lookup lists because there are an almost infinite number of thicknesses. Is there anything I can add to my formula to make it remove the inch mark if one is present? Is there anther solution? Many thanks in advance. -- pdgood ------------------------------------------------------------------------ pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623 View this thread: http://www.excelforum.com/showthread...hreadid=539976 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() How about Data Validation Select the cell that is used for the thickness input Go to Data Validation On the Settings tab under "Allow" choose decimal Set your Maximum and Minimum values which would cover acceptable entries Then click the Input Message tab and type a message which will show up when the user selects that cell - something like "Thickness Required - Type numeric characters only" Then on the Warning tab type in another message which will show up if your user fails to follow the first message - something like "Invalid Entry - Do not type non-numeric characters" Then click OK -- Cutter ------------------------------------------------------------------------ Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848 View this thread: http://www.excelforum.com/showthread...hreadid=539976 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You could use data validation for this perhaps. Under 'data'....'validation'
you can allow for a decimal number. It will ask you to enter a range (just choose the min and max possible numbers they could enter) Then in the error notification field type something like 'Invalid entry, do not use " inches symbol.' since putting the " at the end makes the data alphanumberic rather than just decimal it will not allow them to enter the number with the " "pdgood" wrote: Hi, In cells D4:D13 I ask the users of this Excel sheet to type in a thickness of material. This is a decimal followed by three digits. Example: .250 But if they type in the inch mark after the thickness, it messes up the formula I have in related cells E4:E13. For example: In E4 I have =(IF(D4<=0.225,Pricing!C2,IF((D4=0.301),Pricing!C 37,Pricing!C19))) All is well if the user types in something like .250, but if they type in .250" my formula does not work. I can't use drop down menus or Lookup lists because there are an almost infinite number of thicknesses. Is there anything I can add to my formula to make it remove the inch mark if one is present? Is there anther solution? Many thanks in advance. -- pdgood ------------------------------------------------------------------------ pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623 View this thread: http://www.excelforum.com/showthread...hreadid=539976 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
convert decimal to inch & fraction | Excel Discussion (Misc queries) | |||
How do I insert a dot/check mark in a cell in Excel? | New Users to Excel | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) | |||
Can tick mark labels be flipped about axis? | Charts and Charting in Excel | |||
HOW DO I USE WATER MARK " DRAFT" IN EXEL | New Users to Excel |