Deactivating Cells Dependant on Another Cells Value

[94thminute]

Hi,

Basically I've been told to make a sheet completely idiot proof, so that data can be put into it without the ability to make mistakes.

The Questionairre has questions such as if yes go to question 6, if no go to question 8.

Therefore I want to make it so say for question 5, the answer is no, the next 2 cells are locked, or made grey, or anything so that people know to not continue putting data into them. I hope that makes sense.

I'm using Excel 2010, if that makes a difference.

Thanks in advance

 

[94thminute]

Hi,

Basically I've been told to make a sheet completely idiot proof, so that data can be put into it without the ability to make mistakes.

The Questionairre has questions such as if yes go to question 6, if no go to question 8.

Therefore I want to make it so say for question 5, the answer is no, the next 2 cells are locked, or made grey, or anything so that people know to not continue putting data into them. I hope that makes sense.

I'm using Excel 2010, if that makes a difference.

Thanks in advance

One other thing perhaps worth mentioning:

I've looked at conditional formatting, the realms of if A2 > 2 then grey out the next 3 cells, which works okay... except I don't know how to make a formula to say if A2 is numbers 1,3,4,5,6 then do something, and also don't know how to make that rule effective for multiple rows either.

 

[SteveO59L]

You will need to do this using VBA

 

[SuperFerret]

You could make Excel bring up an Error Message if they try to enter in a value after selecting N in the other column using Data Validation?

Select the cell you don't want them to fill, Data Validation > Custom
=AND(LEN(E1)=0,D1="N")

Where D1 is the value that should be N (Question 5 answer) and E1 is Question 6 answer.

Select Error Alert to Stop and add a message

Would this work for you?

EDIT: Formula may need testing (I haven't had chance)

 

[94thminute]

You could make Excel bring up an Error Message if they try to enter in a value after selecting N in the other column using Data Validation?

Select the cell you don't want them to fill, Data Validation > Custom
=AND(LEN(E1)=0,D1="N")

Where D1 is the value that should be N (Question 5 answer) and E1 is Question 6 answer.

Select Error Alert to Stop and add a message

Would this work for you?

EDIT: Formula may need testing (I haven't had chance)

Wow! That is genuinely amazing, the code makes no sense to me, and yet it works. Thank you!

 

[SuperFerret]

Make sure you test it, I've just had a try and in some instances it fails and generates the error even if the Q5 answer is Y

I'm trying to tweak now

 

[94thminute]

Wow! That is genuinely amazing, the code makes no sense to me, and yet it works. Thank you!

Oh, wait... I tried putting a value to lock the cell in, and it worked... it seems to have locked the cell for anything though; problem slightly less solved.

I've come across conditional formatting, and can make cells go grey in colour if the entry is a 1 when it should be a 2, or something else

I want to make it work for over 100 rows though, is there anyway of copying and pasting condtional formatting in the same way I would a formula?

Eg, if I Grey out B1 when A1 is a '1', then copy and drag the formatting from A1 to A2, so that the formula changes to cover cells B2 on A2's entry...

The best I can come up with is greying out 100 cells on the answer to A1

I hope that makes some sense?

 

[SuperFerret]

Oh, wait... I tried putting a value to lock the cell in, and it worked... it seems to have locked the cell for anything though; problem slightly less solved.

Yes, I'm just looking into a resolution for that...

...is there anyway of copying and pasting condtional formatting in the same way I would a formula?

Yes, there should be something called Format Painter that if you select your Conditionally Formatted cell and hit Format Painter, you can effectively paste the conditional formatting

 

[SuperFerret]

Eurgh... I think I needed coffee

Try validation custom =D1="Y" with the Error Message...

Realised I was overcomplicating it basically this will say if D1 (Q5 answer) is Y then allow whatever you want in this cell, otherwise if will error...

 

[94thminute]

Eurgh... I think I needed coffee

Try validation custom =D1="Y" with the Error Message...

Realised I was overcomplicating it basically this will say if D1 (Q5 answer) is Y then allow whatever you want in this cell, otherwise if will error...

Yes! That one works absolutely fantastically, thank you so much for your help