Data Validation - Drop Down

How can I "disable" a Drop down list (but still have it in use if the
overall Sheet protection is off)?

I thought it would work via the Sheet Protection, but my sheet is protected
(as are the cells locked where the Drop down is) but I still get the option
of picking from the DV Drop down

Thanks

Hi John,

I suspect that drop down cell was unlocked before sheet protection was
applied.

To start over unprotect the sheet and select the drop down cell. Under
Format > Cells > Protection > chect the Locked box > OK.

Now protect the sheet under Tools > Protect Sheet.

I hope I steered you correctly.

HTH
Regards,
Howard

"John" <[email protected]> wrote in message
news:[email protected]...
> How can I "disable" a Drop down list (but still have it in use if the
> overall Sheet protection is off)?
>
> I thought it would work via the Sheet Protection, but my sheet is
> protected (as are the cells locked where the Drop down is) but I still get
> the option of picking from the DV Drop down
>
> Thanks
>
>
>

Thanks Howard

From reading back in the Group, I don't think you can actually protect a
cell from entry with DV, thus I used the formula below as a work around,
esentially if No is in cell AT1 then the DV is 'unlocked' anything else mean
its locked from selection

=OFFSET(Times,0,0,(COUNTA(Times,"<>")-1)*($AT$1="No"),1)



"L. Howard Kittle" <[email protected]> wrote in message
news:[email protected]...
> Hi John,
>
> I suspect that drop down cell was unlocked before sheet protection was
> applied.
>
> To start over unprotect the sheet and select the drop down cell. Under
> Format > Cells > Protection > chect the Locked box > OK.
>
> Now protect the sheet under Tools > Protect Sheet.
>
> I hope I steered you correctly.
>
> HTH
> Regards,
> Howard
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> How can I "disable" a Drop down list (but still have it in use if the
>> overall Sheet protection is off)?
>>
>> I thought it would work via the Sheet Protection, but my sheet is
>> protected (as are the cells locked where the Drop down is) but I still
>> get the option of picking from the DV Drop down
>>
>> Thanks
>>
>>
>>
>
>

In Excel 2000 and earlier versions, you can change the selection in a
locked cell's data validation dropdown, if the list is from a range on
the worksheet.

If the list is typed in the data validation dialog box, the selection
can't be changed.

In Excel 2002 and later versions, neither type of dropdown list can be
changed if the cell is locked and the sheet is protected.

This MSKB article has information on the previous behaviour:

XL97: Error When Using Validation Drop-Down List Box
http://support.microsoft.com/default.aspx?id=157484

John wrote:
> How can I "disable" a Drop down list (but still have it in use if the
> overall Sheet protection is off)?
>
> I thought it would work via the Sheet Protection, but my sheet is protected
> (as are the cells locked where the Drop down is) but I still get the option
> of picking from the DV Drop down
>
> Thanks
>
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html