Data validation one cell depending on value other cell

[Lafalaise]

Hi , hope someone can help me with following Data validation problem:

Problem 1:
I have data validation in a matrix allowing a user to enter only a N or R.
By processing a macro I later change the N's into X's and remove R's.
When the file is saved, it is send back to the user for a next change.
No issue here, but the user is able to copy and paste the X's of the previous version anywhere in the matrix, which I don't want.

Problem 2:
- In cell let's say F1 if have a data validation for 3 option: A, B, C
- In cell V1 is have a date validation to enter only an N or R
Depending on the value in F1, say A, the user is allowed to enter N or R. If the value <>A the user should not be allowed to enter anything

The sheet is password protected, but the cells for this data entry are unlocked.

Thanks for any help!

 

[marc j]

Hello and welcome,
I can guide you to find the answer to your problem 2. What you are looking for is "dependent datavalidation."
If you do a search you find many "excellers" (MrExcel, Excelisfun, Contextures etc all have videos.)

I also found a website by one of the board members, Excel Tip #13 – Creating a Dependent Validation Drop-down List
The presentation of the information is nice and clear.

I hope that helps,
marcj

 

[Lafalaise]

Hi Marcj,

Thanks for your quick response. I'll check this out immediately!

Lafalaise

Hello and welcome,
I can guide you to find the answer to your problem 2. What you are looking for is "dependent datavalidation."
If you do a search you find many "excellers" (MrExcel, Excelisfun, Contextures etc all have videos.)

I also found a website by one of the board members, Excel Tip #13 – Creating a Dependent Validation Drop-down List
The presentation of the information is nice and clear.

I hope that helps,
marcj

 

[Lafalaise]

Checked it out. It is regarding problem 2, but unfortunatly doesn't do what I need. I'll try to explain better:
In one column (F) I have data validation: A list contains 3 values; A, B, C
In columns V- etc (around 130 columns) the user is able to enter a N or a R.
Enter a R is no issue. It is always possible
Enter a N is conditional for about 50 of these 130 columns. If N is allowed, the value on column F could be a A, B or C. The validation on this is per column in the 50 columns.
The validation is per line.

Example: Value A in cell F7. The user can enter a N in cell V7, but not in cell X7
Example: Value B in cell F8. The user can enter a N in cell X8, but not in cell V8
Example: Value A, B or C in cell F9. The user can enter a N in cell Z as this cell is not validated.
Blank cells in column V etc are allowed.

So dependent data validation has to be set per cell, starting column V, depending on the value in column F

Hope this clarfies better