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

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

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