Conditional Formatting

[GMc]

Is there any way to conditionally block data entry to a cell (lock it)
until data is entered in other cells. Once data has been entered into
the pre-quailifying cells I want to open up the locked cell to allow
data entry.

Thanks GMc

 

[T. Valko]

You can use data validation but it has limitations.

Assume you don't want to allow an entry in cell B1 until there is an entry
in cell A1.

Select cell B1
Goto the menu Data>Validation
Allow: Custom
Formula: =COUNTA(A1)
OK

Limitations:

It's possible to copy/paste into cell B1
It's possible to drag-n-drop into cell B1
If there's an entry in cell A1 then cell B1 will accept an entry. However,
if the entry is deleted in cell A1 the entry remains in cell B1.

 

[GMc]

You can use data validation but it has limitations.

Assume you don't want to allow an entry in cell B1 until there is an entry
in cell A1.

Select cell B1
Goto the menu Data>Validation
Allow: Custom
Formula: =COUNTA(A1)
OK

Limitations:

It's possible to copy/paste into cell B1
It's possible to drag-n-drop into cell B1
If there's an entry in cell A1 then cell B1 will accept an entry. However,
if the entry is deleted in cell A1 the entry remains in cell B1.

--
Biff
Microsoft Excel MVP







- Show quoted text -

Biff et al.,

The cell I wish to "lock" already has a data validation parameter
(forcing an input of a date between a range of dates). Is there any
way to have 2 validations on the same cell ?

Thanks

Grant

 

[T. Valko]

You might be able to consolidate both conditions in a single formula.

=AND(COUNTA(A1),B1>=DATE(2008,1,1),B1<=DATE(2008,1,31))

--
Biff
Microsoft Excel MVP

You can use data validation but it has limitations.

Assume you don't want to allow an entry in cell B1 until there is an entry
in cell A1.

Select cell B1
Goto the menu Data>Validation
Allow: Custom
Formula: =COUNTA(A1)
OK

Limitations:

It's possible to copy/paste into cell B1
It's possible to drag-n-drop into cell B1
If there's an entry in cell A1 then cell B1 will accept an entry. However,
if the entry is deleted in cell A1 the entry remains in cell B1.

--
Biff
Microsoft Excel MVP







- Show quoted text -

Biff et al.,

The cell I wish to "lock" already has a data validation parameter
(forcing an input of a date between a range of dates). Is there any
way to have 2 validations on the same cell ?

Thanks

Grant

 

[GMc]

You might be able to consolidate both conditions in a single formula.

=AND(COUNTA(A1),B1>=DATE(2008,1,1),B1<=DATE(2008,1,31))

--
Biff
Microsoft Excel MVP







Biff et al.,

The cell I wish to "lock" already has a data validation parameter
(forcing an input of a date between a range of dates). Is there any
way to have 2 validations on the same cell ?

Thanks

Grant- Hide quoted text -

- Show quoted text -

Biff, Thanks again, the AND function works for me. much appreciated.
Grant

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

You might be able to consolidate both conditions in a single formula.

=AND(COUNTA(A1),B1>=DATE(2008,1,1),B1<=DATE(2008,1,31))

--
Biff
Microsoft Excel MVP







Biff et al.,

The cell I wish to "lock" already has a data validation parameter
(forcing an input of a date between a range of dates). Is there any
way to have 2 validations on the same cell ?

Thanks

Grant- Hide quoted text -

- Show quoted text -

Biff, Thanks again, the AND function works for me. much appreciated.
Grant