Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Restrict the information that can be written in a range

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to restrict the information that can be written in a range. Per example in A1, I want just numbers but no more than 10 characters and A2 I want alphanumeric but no more than 60 characters and also don't allow apecial characters like "," "%", "&".

    Also if it is possible to show a message when you write a character not allowed.


    Any ideas??????


    Hugo

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    409
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For A1 use Data Validation, select "Custom" and enter this in "Formula":
    =AND(ISNUMBER(A1),LEN(A1)<=10)

    You can enter a message and/or error alert as well.

    Determining whether an entry in A2 is alphanumeric is beyond me.

  3. #3
    Guest

    Default

    On 2002-02-22 17:14, Scott R wrote:
    For A1 use Data Validation, select "Custom" and enter this in "Formula":
    =AND(ISNUMBER(A1),LEN(A1)<=10)

    You can enter a message and/or error alert as well.

    Determining whether an entry in A2 is alphanumeric is beyond me.
    Scott:

    Can you be more specific, I mean what is "data Validation" are you talking about excel?????

    Hugo

  4. #4
    Guest

    Default

    On your menu bar, click tools -> data validation. Select formula on the first of three tabs and enter the formula. The second tow tabs are the message prompts.

    Cheers

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,644
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-02-22 13:38, huorsa wrote:
    I want to restrict the information that can be written in a range. Per example in A1, I want just numbers but no more than 10 characters and A2 I want alphanumeric but no more than 60 characters and also don't allow apecial characters like "," "%", "&".

    Also if it is possible to show a message when you write a character not allowed.


    Any ideas??????


    Hugo
    What is desired wrt A1 is easy (as Scott R noted), wrt A2 complicated in that it cannot be handled directly from within the Data Validation. In what follows I'll assume A1 and A2 to be points of data entry by users, say, in Sheet1.

    Sheet1:

    Activate A1;
    Go to the Name Box on the Formula Bar, type an appropriate name, e.g., NumEntry, followed by enter.

    Activate A2;
    Go to the Name Box on the Formula Bar, type an appropriate name, e.g., AlphaNumEntry, followed by enter.

    Insert a new worksheet or rename an existing empty worksheet BBoard (from Blackboard).

    BBoard:

    In B1 enter: Is NumEntry OK?
    In B2 enter: Is AlphaNumEntry OK?

    In C1 enter:

    =AND(ISNUMBER(NumEntry),LEN(NumEntry)<=10)+0

    Activate C1; Go to the Name Box on the Formula Bar, type NumEntryOK?, followed by enter.

    In C2 enter:

    =(SUMPRODUCT((ISNUMBER(MATCH(CODE(MID(UPPER(AlphaNumEntry),ROW(INDIRECT("1:"&LEN(AlphaNumEntry))),1)),{48,49,50,51,52,53,54,55,56,57,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,8 6,87,88,89,90},0)))+0)=LEN(AlphaNumEntry))+0

    Activate C2; Go to the Name Box on the Formula Bar, type AlphaNumEntryOK?, followed by enter.

    Select C1:C2;
    Activate Format|Cells;
    On the Number tab, choose Custom;
    Enter for Type:

    [=0]"No";[=1]"Yes";General

    Back to Sheet1:

    Activate A1, the cell we named NumEntry;
    Activate the option Data|Validation;
    On the Settings tab, choose Custom for Allow;
    Enter what follows in the Formula box:

    =NumEntryOK?=1

    Click OK.

    Activate A2, the cell we named AlphaNumEntry;
    Activate the option Data|Validation;
    On the Settings tab, choose Custom for Allow;
    Enter what follows in the Formula box:

    =AlphaNumEntryOK?=1

    Click OK.

    In the last two steps, you have the choice to set up a message on the Alerts tab of the Data Validation Window to tell the user what you expect them to enter in the data entry cells.

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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