Making cells conditionally inaccesible

[berkeleydave]

Hello,

I was hoping someone would be to help me with the following: I am working with an attendance sheet for prospective after-school program participants. Every sheet has the full list of all of the potential students. On the very first of the worksheets, I will mark their attendance or note of they are either a) not enrolled or b) not participating. What I would like is for all of the subsequent worksheets to respond to me entering "Not Enrolled" or "Not Participating" in the first worksheet by filling the row that that student's name is in with a particular color and making all of the cells corresponding to that row inaccessible (e.g. the rows are filled black and the cells are blank and cannot be altered from being blank). Is this possible?

Thanks in advance!

 

[Damon Ostrander]

Hi berkeleydave,

What you describe is easily done via a VBA macro that colors and locks the rows corresponding to the attendance note, then protects the worksheets. Unfortunately, I don't have time to code this for you right now (and would need a bit more information from you anyway), but rest assured that this is not a difficult problem and that someone with a thorough knowledge of Excel and VBA could knock it out in probably less than an hour.

Damon

 

[mikerickson]

You can do this with names.
If the column with the values is column A of sheet1.
Select cell A1 and define a name

Name: Attendence RefersTo: =Sheet1!$A1

Then cells with the conditional format
=OR((Attendence="not enrolled"),(Attendence="not participating"))
will be colored appropriatly.

The user will not be permitted to enter data into cells with the validation formula
=AND((Attendence<>"not enrolled"),(Attendence<>"not participating"))

 

[berkeleydave]

Thank you! That validation basically did the trick.

The next step for me is to figure out how to achieve the same thing without allowing the user to enter the cell. What I mean is that while the validation you suggested won't allow any entry to remain in the cell, it still allows the user to enter text into the cell (and then get an error message). I am guessing this is some sort of conditional cell protection which I saw some other threads on.