To lock/unlock a cell based on value in another cell

[optimus]

Hello Friends,


I have another problem with respect unlocking/locking of cells based on conditions. Following are the details:


Below are the columns in my sheet.


Column1 Cloumn2 Column3 Column4 Column5

------- ------- ------- ------- -------

Event 1 True

Event 2 False

Event 3 True


Column1 is a drop down list. Every event in this drop down list corresponds to only one of the three columns e.g. Column3, Column4 and Column5.


Now,


IF(Column1="Event1" and Column2="False") THEN


All the columns from 3 to 5 would be disabled.

Also,


IF(Column1="Event1" and Column2="True") THEN


lock/disable the all columns from 3 to 5 except the one which corresponds to Event1.


Any Suggestions. Thanks.

 

[Luke M]

Could set this up as data validation actually. Assuming the column has some sort of header letting us know which one corresponds with Event1, Data validation rule would be something like:

=AND(C$2=$A2,$B2)


If there are no headers, you can still get it to work by doing this:

=AND(RIGHT($A2,1)=COLUMN(A$1),$B2)

 

[optimus]

Thanks Luke. I tried the your formula but it did not work. Following details might throw some light;


Event E-mail id | Date |Birthday |Marriage Anniversary | Christmas | New Year

Marriage Anniversary

Birthday

Marriage Anniversary

Christmas

Newyear


1) Event Column contains the drop down list of all the events.

2) Date column can have either of the two entries "True" (if entry is today's date+15 days) or else "False"

3) The columns after date basically act as controls and hyperlinks to send a mail to the e-mail id mentioned in the column "E-mail-id".

4)Now, lets say for Cell(A2,1)= Marriage Anniversary and Cell(C2,1)="True" then only the entry in the column "Marriage Anniversary" would be active and the user will be able to send a mail.

5) Now, lets say for Cell(A2,1)= Marriage Anniversary and Cell(C2,1)="False" all the columns from Birthday onwards will be inactive.


I hope this brings some clarity, Luke. Thanks for your time.

 

[Luke M]

Sounds then like you're actually wanting the cells to display something different, in which case, maybe a IF function?


=IF(AND(D$2=$A2,CB2),HYPERLINK("mailto:"&B2,"Send email"),"")


Copied to the right and down, this would show a hyperlink in any applicable cell that would let you send an email.

 

[optimus]

Thanks a ton, Luke.It worked! I now only have the problem of inserting a picture in this automated mail.