Results 1 to 3 of 3
  1. #1

    restrict entries in column

    Im running Excel97 SR1 and have palyed around with COUNTA to restrict the number of entries in a column to one. The sheet allows users to choose a serice down the left side and to specify a time in a column that they would like to perform the service. How can I limit them to one service per time slot? They just type an X to select the time. COUNTA just keeps giving errors.
    Thank you for any ideas.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: restrict entries in column

    The following formula will put a message in the cell it is entered in if the number of X's in B2 through B10 is more than one:

    <pre>=IF(COUNTA(B2:B10)>1,"Only one service allowed","")

    That won't prevent it, it will just display the message. If you want to prevent it, you will need to write code in the WorksheetChange event procedure.
    Legare Coleman

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Dublin, Ireland, Republic of
    Thanked 0 Times in 0 Posts

    Re: restrict entries in column

    You could set up an additional column with the following typr of formula
    =IF(COUNTA(C2:M2)=0,"X","") and use the results of that formula as the basis for a Data Validation. In Tools, Data, Validation select allow List, and enter =$B2 (for example). Copy that to all the cells involved, and once an entry is made in one row, no further entries should be accepted.

    Attached is a workbook to demonstrate. I used column B (which could be hidden) for the formula, and applied the validation from C to M.

    Hope it is useful

    Andrew C
    Attached Files Attached Files

Posting Permissions

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