Cell locking dependent on Cell value
 

Hi Guys I always seem to have questions that are complex...

What im looking to do On a standard sheet Various cells. can i have a cell
that will not allow you to edit it unless another cell value is a spcified
Value.. basically a password thing.... what i am doing is this.

I have a Sheet whete cell C2 is where i want the user to enter a password
from several or even just one that i have defined. should that cell have the
correct value then cell D2 will allow you to edit its value. else the cell
should be locked for editing... is this possable? and how would i do it....
and please tell me you can do it without VB scripting.

Cell locking dependent on Cell value
 

Gareth - Network analyst. wrote:
Hi Guys I always seem to have questions that are complex...

What im looking to do On a standard sheet Various cells. can i have a cell
that will not allow you to edit it unless another cell value is a spcified
Value.. basically a password thing.... what i am doing is this.

I have a Sheet whete cell C2 is where i want the user to enter a password
from several or even just one that i have defined. should that cell have the
correct value then cell D2 will allow you to edit its value. else the cell
should be locked for editing... is this possable? and how would i do it....
and please tell me you can do it without VB scripting.

Hi Gareth

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...

Select D2| Go Data|Validation|Select Custom from the bottom of the list
of types of validation|deselect "Ignore Blanks"| type in the formula...
=OR($C$2="Password1",$C$2="Password2",$C$2="Passwo rd3")

where Password1, Password2, Password3 are three passwords that you
would accept.

You can also add a message requesting the password be entered first.

D2 is editable, however the added change is not accepted without an
acceptable password in C2.


Ken Johnson

Cell locking dependent on Cell value
 

Ken Johnson wrote:

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.

Cell locking dependent on Cell value
 

"Paul Lautman" wrote:

Ken Johnson wrote:

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.

Ok that Works But one little problem that i found here now...
OK so C2 my password cell is the same color as the background and is locked
to the font i set. now in D2 i got the Validation running stating EG.
=OR($C$2="password"). ok no problem

BUT now i have a cell D3 that has a Vlookup dependent on the cell value in
D2 i just tested it if i enter this lookup value say D3 is
=VLOOKUP(C3,Passwords!A:L,2,FALSE) the Value in the look up will display with
the error for invalid password......

How do i bypass this.

Cell locking dependent on Cell value
 

Paul Lautman wrote:
Ken Johnson wrote:

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.

Thanks for that Paul.
Very niffty solution.
Ken Johnson

Cell locking dependent on Cell value
 

"Gareth - Network analyst." wrote:



"Paul Lautman" wrote:

Ken Johnson wrote:

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.

Ok that Works But one little problem that i found here now...
OK so C2 my password cell is the same color as the background and is locked
to the font i set. now in D2 i got the Validation running stating EG.
=OR($C$2="password"). ok no problem

BUT now i have a cell D3 that has a Vlookup dependent on the cell value in
D2 i just tested it if i enter this lookup value say D3 is
=VLOOKUP(C3,Passwords!A:L,2,FALSE) the Value in the look up will display with
the error for invalid password......

How do i bypass this.


Bingo I worked it out... Just for added Security you also add the If
Statment into my D3 cell it looks like this.
=IF(C2="password",(VLOOKUP(C3,Passwords!A:L,2,FALS E)),"#N/A")
Actually works a bit better than the Validation lookup... you just add the
Validation for Extra protection.

Thanks all for the assist and the suggestions. :-)

Cell locking dependent on Cell value
 

Gareth - Network analyst. wrote:
"Gareth - Network analyst." wrote:



"Paul Lautman" wrote:

Ken Johnson wrote:

Would Custom Data Validation be good enough. The Password in C2 will
always be visible in C2 while D2 is accepting the user's changes
though...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.

Ok that Works But one little problem that i found here now...
OK so C2 my password cell is the same color as the background and is locked
to the font i set. now in D2 i got the Validation running stating EG.
=OR($C$2="password"). ok no problem

BUT now i have a cell D3 that has a Vlookup dependent on the cell value in
D2 i just tested it if i enter this lookup value say D3 is
=VLOOKUP(C3,Passwords!A:L,2,FALSE) the Value in the look up will display with
the error for invalid password......

How do i bypass this.


Bingo I worked it out... Just for added Security you also add the If
Statment into my D3 cell it looks like this.
=IF(C2="password",(VLOOKUP(C3,Passwords!A:L,2,FALS E)),"#N/A")
Actually works a bit better than the Validation lookup... you just add the
Validation for Extra protection.

Thanks all for the assist and the suggestions. :-)

I'm glad you got that solved Gareth.
I read your last post over and over and was getting absolutely nowhere.

Thanks for the feedback anyway.

Ken Johnson