+ Reply to Thread
Results 1 to 5 of 5

Data Validation w/custom + w/list

Hybrid View

  1. #1
    Keith
    Guest

    Data Validation w/custom + w/list

    Two cells, using data validation | custom... COUNTA($A1:$B1)<2
    This works fine to prevent data in both cells.
    But I also want to use a drop down list for the data selection in A1 and B1,
    How can I do both?
    Thanks,
    Keith




  2. #2
    Debra Dalgleish
    Guest

    Re: Data Validation w/custom + w/list

    Create a named range with your list of options, e.g. MyList. There are
    instructions here:

    http://www.contextures.com/xlNames01.html

    Choose Insert>Name>Define
    Type the name: NoList
    In the Refers to box, type:

    =OFFSET(Sheet1!$G$1,0,0,0,1)
    You can change the Sheet1!$G$1 reference to a sheet and cell
    in your workbook

    Click OK

    Select cells A1:B1
    Choose Data>Validation
    From the Allow dropdown, select List
    In the Source box, type:

    =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)

    Click OK

    Keith wrote:
    > Two cells, using data validation | custom... COUNTA($A1:$B1)<2
    > This works fine to prevent data in both cells.
    > But I also want to use a drop down list for the data selection in A1 and B1,
    > How can I do both?
    > Thanks,
    > Keith
    >
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    Keith
    Guest

    Re: Data Validation w/custom + w/list

    Debra,
    Thanks this works fine. Is there a way to have an Error Alert generated when
    counta =1 (when the one of the cells has data selected from the list)?

    Thanks,
    Keith



    "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
    news:42725588.3000400@contexturesXSPAM.com...
    > Create a named range with your list of options, e.g. MyList. There are
    > instructions here:
    >
    > http://www.contextures.com/xlNames01.html
    >
    > Choose Insert>Name>Define
    > Type the name: NoList
    > In the Refers to box, type:
    >
    > =OFFSET(Sheet1!$G$1,0,0,0,1)
    > You can change the Sheet1!$G$1 reference to a sheet and cell
    > in your workbook
    >
    > Click OK
    >
    > Select cells A1:B1
    > Choose Data>Validation
    > From the Allow dropdown, select List
    > In the Source box, type:
    >
    > =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)
    >
    > Click OK
    >
    > Keith wrote:
    >> Two cells, using data validation | custom... COUNTA($A1:$B1)<2
    >> This works fine to prevent data in both cells.
    >> But I also want to use a drop down list for the data selection in A1 and
    >> B1,
    >> How can I do both?
    >> Thanks,
    >> Keith
    >>
    >>
    >>

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >




  4. #4
    Debra Dalgleish
    Guest

    Re: Data Validation w/custom + w/list

    You can add an Error Alert Message, in the data validation dialog box.

    Also, I've revised my validation setup (changes are marked '>>):

    Create a named range with your list of options, e.g. MyList.
    There are instructions here:
    http://www.contextures.com/xlNames01.html

    '>> In cell G1, type: =""

    Choose Insert>Name>Define
    Type the name: NoList
    '>> In the Refers to box, type: =Sheet1!$G$1
    You can change the Sheet1!$G$1 reference to a sheet and cell
    in your workbook
    Click OK

    Select cells A1:B1
    Choose Data>Validation
    From the Allow dropdown, select List
    In the Source box, type:
    =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)
    '>> Remove the check mark from 'Ignore Blanks'
    Click OK

    Keith wrote:
    > Debra,
    > Thanks this works fine. Is there a way to have an Error Alert generated when
    > counta =1 (when the one of the cells has data selected from the list)?
    >
    > Thanks,
    > Keith
    >
    >
    >
    > "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
    > news:42725588.3000400@contexturesXSPAM.com...
    >
    >>Create a named range with your list of options, e.g. MyList. There are
    >>instructions here:
    >>
    >> http://www.contextures.com/xlNames01.html
    >>
    >>Choose Insert>Name>Define
    >>Type the name: NoList
    >>In the Refers to box, type:
    >>
    >> =OFFSET(Sheet1!$G$1,0,0,0,1)
    >> You can change the Sheet1!$G$1 reference to a sheet and cell
    >> in your workbook
    >>
    >>Click OK
    >>
    >>Select cells A1:B1
    >>Choose Data>Validation
    >>From the Allow dropdown, select List
    >>In the Source box, type:
    >>
    >> =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)
    >>
    >>Click OK
    >>
    >>Keith wrote:
    >>
    >>>Two cells, using data validation | custom... COUNTA($A1:$B1)<2
    >>>This works fine to prevent data in both cells.
    >>>But I also want to use a drop down list for the data selection in A1 and
    >>>B1,
    >>>How can I do both?
    >>>Thanks,
    >>>Keith
    >>>
    >>>
    >>>

    >>
    >>
    >>--
    >>Debra Dalgleish
    >>Excel FAQ, Tips & Book List
    >>http://www.contextures.com/tiptech.html
    >>

    >
    >
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  5. #5
    Keith
    Guest

    Re: Data Validation w/custom + w/list

    Thanks, appreciate your help
    Keith


    "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
    news:42739D28.4070408@contexturesXSPAM.com...
    > You can add an Error Alert Message, in the data validation dialog box.
    >
    > Also, I've revised my validation setup (changes are marked '>>):
    >
    > Create a named range with your list of options, e.g. MyList.
    > There are instructions here:
    > http://www.contextures.com/xlNames01.html
    >
    > '>> In cell G1, type: =""
    >
    > Choose Insert>Name>Define
    > Type the name: NoList
    > '>> In the Refers to box, type: =Sheet1!$G$1
    > You can change the Sheet1!$G$1 reference to a sheet and cell
    > in your workbook
    > Click OK
    >
    > Select cells A1:B1
    > Choose Data>Validation
    > From the Allow dropdown, select List
    > In the Source box, type:
    > =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)
    > '>> Remove the check mark from 'Ignore Blanks'
    > Click OK
    >
    > Keith wrote:
    >> Debra,
    >> Thanks this works fine. Is there a way to have an Error Alert generated
    >> when counta =1 (when the one of the cells has data selected from the
    >> list)?
    >>
    >> Thanks,
    >> Keith
    >>
    >>
    >>
    >> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
    >> news:42725588.3000400@contexturesXSPAM.com...
    >>
    >>>Create a named range with your list of options, e.g. MyList. There are
    >>>instructions here:
    >>>
    >>> http://www.contextures.com/xlNames01.html
    >>>
    >>>Choose Insert>Name>Define
    >>>Type the name: NoList
    >>>In the Refers to box, type:
    >>>
    >>> =OFFSET(Sheet1!$G$1,0,0,0,1)
    >>> You can change the Sheet1!$G$1 reference to a sheet and cell
    >>> in your workbook
    >>>
    >>>Click OK
    >>>
    >>>Select cells A1:B1
    >>>Choose Data>Validation
    >>>From the Allow dropdown, select List
    >>>In the Source box, type:
    >>>
    >>> =IF(COUNTA($A$1:$B$1)=0,MyList,NoList)
    >>>
    >>>Click OK
    >>>
    >>>Keith wrote:
    >>>
    >>>>Two cells, using data validation | custom... COUNTA($A1:$B1)<2
    >>>>This works fine to prevent data in both cells.
    >>>>But I also want to use a drop down list for the data selection in A1 and
    >>>>B1,
    >>>>How can I do both?
    >>>>Thanks,
    >>>>Keith
    >>>>
    >>>>
    >>>>
    >>>
    >>>
    >>>--
    >>>Debra Dalgleish
    >>>Excel FAQ, Tips & Book List
    >>>http://www.contextures.com/tiptech.html
    >>>

    >>
    >>
    >>

    >
    >
    > --
    > Debra Dalgleish
    > Excel FAQ, Tips & Book List
    > http://www.contextures.com/tiptech.html
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1