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

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,
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
>

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

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
>