Data Validation w/custom + w/list

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

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
 
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
 
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
 
Thanks, appreciate your help
Keith


Debra Dalgleish said:
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
Click to expand...
 
Back
Top