Cell locking dependent on Cell value

  • Thread starter Gareth - Network analyst.
  • Start date
G

Gareth - Network analyst.

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.
 
K

Ken Johnson

Gareth said:
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.
Click to expand...

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="Password3")

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
 
P

Paul Lautman

Ken said:
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...
Click to expand...
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.
 
G

Gareth - Network analyst.

Paul Lautman said:
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.
Click to expand...

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.
 
K

Ken Johnson

Paul said:
Not if you format C2 to have the same colour text as background or give it a
font seting of wingdings.
Click to expand...

Thanks for that Paul.
Very niffty solution.
Ken Johnson
 
G

Gareth - Network analyst.

Gareth - Network analyst. said:
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.
Click to expand...


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,FALSE)),"#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. :)
 
K

Ken Johnson

Gareth said:
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,FALSE)),"#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. :)
Click to expand...

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
 
Top