How to use data validation to restrict data entry based on another cell's value

Hi,

I would like to know how to use Data Validation feature to restrict data entry of certain cells based on the value of a control cell.

Specifically, in my scenario, I have a cell e.g., A1, where I want to be able to enter a number range of 1 through 4.

Based on A1, I want B1:B4 to allow or restrict data entry based on the number value of A1. E.g., if A1=4, B1:B4 will allow data entry. If A1=1, only B1 will allow data entry. Furthermore, if A1 does not have any value inputted or if value inputted is not equal to numbers 1 through 4, B1:B4 will not allow any data entry.

Looking for the best way to do this? Thank you for your help!

I am currently applying to A1 criteria whole number between 1 and 4, and for B1 "=AND(A1>=1,A1<=4)", B2 "=AND(A1>=2,A1<=4)", etc.

However, the issue I'm running into now is that if I change A1 from 2 to 1, B2 will still have the data remaining in the cell. Is there a way to "refresh" the validation? I'd rather avoid the use of macros, but will I need to do so? Thank you!

No, it won't refresh until the cell is selected again.

What you can do is to use a formula in the adjacent cells (column C) to highlight when the entries in column B become invalid due to a change in A1 - you can use a red bolded message to alert the user to the fact that the cell is no longer valid. Another approach is to use conditional formatting on the cells in column B to change the background colour to red, to indicate that they need to be changed.

Hope this helps.

Pete