Locking cells based on other cell value

[smalls]

Hi everyone,

I have several columns into which data will be input.

A and B have unique identifiers (student code and name).
C has data validation with drop down list of choices: New student, Transfer student, Graduated student.
D is "current course selection" and it is a drop-down list. I would like this cell to be blocked to the user if column C holds "graduated student."
E is "previous course selection" and it is also a drop-down list. I would like this cell to be blocked to the user if column C holds "new student."
I would like C, D and E to be blocked to the user if A and B are not filled in.

Is there a way to do this? I've figured out how to conditionally format the cells in D and E based on C, but I can't figure out how to block them.

Thanks!
smalls

[JBeaucaire]

Can you post a sample workbook for us to experiment with?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

[smalls]

Attached is a simplified version of the spreadsheet. I've left notes in the columns indicating what I'm trying to do. Let me know if it's still not clear.

Thanks!
smalls

[JBeaucaire]

The DV formula in C4 is:
=IF(AND($A4<>"",$B$4<>""),Student.type,"-")

In D4 is:
=IF(AND($A4<>"",$B$4<>"",$C4<>"Graduated"),Course.options,"-")

In E4 is:
=IF(AND($A4<>"",$B$4<>"",$C4<>"New"),Course.options,"-")

Copy down as far as you wish.

[smalls]

Hi J,

Thanks very much for your help. Unfortunately, this is not working in the real document, and it's probably my mistake in how I simplified the spreadsheet. There are actually 6 columns of information needed in order to allow the "student type" column to populate--could this be the reason the formula is not working.

Right now it's still allowing me to enter a value into the "student type" column even if the previous columns are empty (you can see an example in the new version I've uploaded which is more similar to my actual sheet)...any other thoughts on how to proceed?

Is there anything like the conditional formatting function that would allow me to do this? Or something like a "locking cell" function?

Thank you again!

smalls

[JBeaucaire]

Similar ideas, we just count and make sure there are 6 values in the cell A:F.

G4:
=IF(COUNTA($A$4:$F$4)=6,Student.type,$J4)

H4:
=IF(AND(COUNTA($A4:$F4)=6,$G4<>"Graduated"),Course.options,"-")

I4:
=IF(AND(COUNTA($A4:$F4)=6,$G4<>"New"),Course.options,"-")