Force Data to certain Format (2000 Sp3)

Topic

New Reply

All,
I have a spreadsheet where data is entered by several folks. In general they get the information entered correctly, however sometimes they miss. The field is a number field but sometimes I need to see N/A. I want to have the cells automatically change to N/A if “na” or “n/a” is entered. I already have three conditional formats set. I could elliminate one, and use the remaining one if anyone can help.

Not sure if contidional formatting is the best method for the above request. Any assistance is greatly appreciated.

Thanks,
Brad

Reply | Quote

Replies

Do you mean the text N/A or the error value #N/A (which has a specific meaning in Excel)?

Reply | Quote

Hans,
The user is entering text = to na or N/A. I just want to ensure the na or n/a is forced to N/A (caps). I do not want the #N/A.

Brad

Reply | Quote

The code below, placed in the worksheet change event routine should do what you asked for column A. If you are entering these values someplace other than column A, then you will need to replace the Range(“A:A”) with the range where the values can be entered.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each oCell In Intersect(Target, Range("A:A"))
If UCase(oCell.Value) = "NA" Or UCase(oCell.Value) = "N/A" Then
oCell.Value = "N/A"
End If
Next oCell
Application.EnableEvents = True
End Sub


To put the code in the worksheet change event routine, right click on the worksheet tab and select “View code”. Paste the routine there.

Reply | Quote

Conditional formatting won’t help. You can use Data | Validation. Say that this is for column D, starting in D2.
– Select D2:D50 or as far down as you want.
– Select Data | Validation…
– Select Custom from the Allow dropdown.
– Enter =OR(ISNUMBER(D2),D2=”N/A”) in the formula box.
– Activate the second tab, and enter an appropriate instruction text.
– Activate the third tab, make sure the Stop type is selected, and enter an appropriate warning to be displayed if the user enters an incorrect value.
– Click OK.

Reply | Quote

Legare and Hans,
Both methods are good. I will use both one for one reason, the other for another issue.

Thanks to both,

Brad

Reply | Quote