Excel Guru Help Shout Out

#1

It seems like something that should be UTTERLY FUCKING SIMPLE isn’t possible, or possibly I am rubbish at Googling for Excel stuff.

Anyway here’s what I want:

If the value in cell $B$2 is one of two possible values then UNprotect cells $C$2 and $D$2 so that the user can select something from the drop downs in those cells. Otherwise leave them protected.

Now the check on $B$2 is trivial but I cannot for the life of me see how I can control the two cells?

#2
#3

Oh fuck VBA.

I had to spend ages at Ofsted 20 years ago trying to fix issues with their huge LEA integrated report generation.

Thanks though. I guess I’ll look it over and see if the effort is worth it (probably won’t be).

#4

Ah. I’d be amazed if it can be done with a formula tbh.

3 Likes
#5

Excel Guru Help Shout Out
Old Blue Last, 8pm, £6/4 NUS

2 Likes
#6

^ this. I’m 99% certain it would require VBA. that said, it should only be a few lines of code. leave it protected as default then get it to run when cell $B$2 changes, if it changes to either option.

1 Like
#7

Could you have those cells unprotected but set up the data validation to only accept certain values instead?

1 Like
#8

yeah, you could put an if clause in the data validation that only gave options on the drop down based on the $B$2 entry ie, if B2 is correct, list the options, otherwise have a blank list.

1 Like
#9

You could have the validation that is based on a list which could be based on formulas, so if one of those two values is selected the validation list contains all the selectable options but if it is not then only contain the original or default value, maybe

#10

Your way is better than mine