Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Force Data to certain Format (2000 Sp3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force Data to certain Format (2000 Sp3)

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

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force Data to certain Format (2000 Sp3)

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force Data to certain Format (2000 Sp3)

    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.

    <code>
    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
    </code>

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Force Data to certain Format (2000 Sp3)

    Conditional formatting won't help. You can use Data | Validation. Say that this is for column D, starting in D2.
    - Select D250 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.

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Force Data to certain Format (2000 Sp3)

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

    Thanks to both,

    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •