Conditional Formatting Complication

Hello everyone, first post. Trying to develop my own user friendly quotation for an application. I often need help and am tired of Googling....

I am trying to develop a quotation, in the quotation a user can choose the thickness of a given material. The given material can be ordered both in inches using a " after the number or in millimeters using the suffix mm. The user can choose the thickness from a drop down menu. This is the easy part.

This is where I have the problem...

The user must be warned that anything over 1 inch or 25.4mm would not work, a conditional format for example that would;

- Warn the user that it is too thick to produce in-house
- Ignore the " or mm on the data from the drop down menu
- and work for either inches or mm

This would be much easier if I just developed a drop down and converted all measurements to either inches or metric, but it will not be simple enough for the user to select. Unfortunately materials are order in both units.

Currently I have the formula =IF(B9>1,"CAN NOT COMPLETE IN HOUSE",""), however it has two problems. One, anything from the B9 drop down menu containing the suffix of " or mm brings a TRUE response - it is not ignoring the type. Secondly the >1 would only work for 1" inch only and not for 25.4 mm. I need it to work for both.

I have no idea on how to do this, perhaps you can help.

Thank you.

Hi Graphic Allen,
Welcome to the Forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Try this...

In A11

Please Login or Register  to view this content.

Is this what you are trying to achieve?