Accept Numeric Only in Protected Spreadsheet

[amy22x3]

I have protected a bidding spreadsheet in it's entirety with the exception of a few cells, with the intention of letting the suppliers populate the unprotected cells with their bid.

My problem is that some suppliers tend to add alpha characters to their numeric bid. For example a bid will be $5.03, but then the supplier will type "USD" behind it. And because of that, I have to remove all the alpha characters before loading the bid data into my software.

Can I just stop this problem even before it starts?

Thanks,

Amy

[NBVC]

You can use Data Validation to force the entry of a number.

Select the cell(s) and go to Data Validation.

From the Allow menu, choose Decimal and then you can choose a Data condition (e.g between, not greater than, etc...) and fill in the appropriate fields...

You can create an input message and an error message through the other tabs in this window.

[Paul]

Hi amy,

You can use Data Validation for this.

1. Select one of your unprotected cells and click Data -> Validation.
2. Beneath 'Allow', choose 'Decimal'
3. Beneath 'Data', choose a setting (Between, Greater Than, etc.)
4. Depending on what you chose, set a minimum and maximum value
5. On the 'Error Alert' tab you can change the settings to show a custom message if you so choose
5. Click OK

Copy that cell to the other cells in which you want to limit to numeric values and it will copy the validation at the same time.

From that point forward you will only be able to enter numbers (with decimals permitted).

[amy22x3]

I've never used Data Validation before, this is awesome!

Thanks