Prevent cells selction of conditionally formatted hidden cells

**jamesjhhill** · 01-28-2011, 04:37 AM

Hi Guys

I have created a spreadsheet to allow our admin guys to input customer and order information which then feeds through onto a 'Pro Forma Invoice', a 'Welcome Letter' and a 'Bank Letter' (see attached)

I have conditionally formatted the input screen so that if Payment Method (top right) says anything but PM 5 (Installments), the 'Standing Order' section disappears.

The problem i have is that you could still select the cells (by mistake) and in doing so affect the other sheets. Is there a way to prevent users selecting a cell if it is hidden by conditional formatting?

Thank you for your help in advance.

**TMS** · 01-28-2011, 04:47 AM

The sheet is password protected but I suspect you could use Data Validation on those cells. It won't prevent the cells being selected but it could stop data being entered.

Use Custom with the formula: =AY4="PM 5 (Installments)"

Regards

**Marcol** · 01-28-2011, 04:53 AM

A password protected sample isn't the handiest way to get your solution.

**jamesjhhill** · 01-28-2011, 05:54 AM

Sorry Guys, i thought i checked every page - clearly not!

**TMS** · 01-28-2011, 07:44 AM

Yes:

Custom - Formula: =$AY$4="PM 5 (Installments)"

On the cells you want to avoid entry

Note the absolute reference rather than my first post

Regards

By the way, the sheet was still protected

**jamesjhhill** · 01-28-2011, 09:15 AM

Thank for that - worked great.

I do have a problem however, the cell in question has already got data validation in it in the form of a list.

Is it possible to apply multiple data validations to one cell?

**TMS** · 01-28-2011, 09:48 AM

I'll have to pass on that ... beyond my level of expertise (at this point in time).

I will experiment, but gut reaction is that it could be difficult.

In the meantime, you could apply the validation to the other cells where you input the address, etc., which could avoid some issues.

Regards

**TMS** · 01-28-2011, 10:07 AM

OK, I can't find a way to use two types of validation but ...

Change BN13 to =IF($AY$4="PM 5 (Installments)",ROW(BN13)-ROW($B$12),"")

And drag down to BN24 (your Installment validation list (inst))

What this will do is set the list to blanks (nulls) so, if you do manage to find that cell you will have nothing to select. You WILL still get a list but all the entries will be blank.

I like the way you've done this but I would be inclined to have the validation lists on a separate hidden control sheet ... personal choice though and it works nicely as it is.

Regards

Regards