Data Validation that changes based on value?

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Column U contains the years people were born. I have a
validation list that limits the user to choose a year
between 1582 and 2100.

I am -trying- to shorten that list for the death. (I am a
genealogist.) If a user selects year 1883 for Cell U10,
then how do I force Cell X10 to allow the user to choose
one of the following: 1883, 1884, 1885,... 1998.

Thus, the user would not have as many years to choose
from, and couldn't pick an "outlandish" death year.

I have tried using data validation in this manner:
Allow: Whole number
Data: between
Minimum: =U9
Maximum: = U9+115

But when I click on Cell X10, there isn't a list.
Obviously, I am doing something wrong, but I don't see my
mistake yet.

Thank you.
 
On another sheet in the workbook, enter a list of dates,
e.g. 1582 to 2215.
Select the list, and click in the name box, to the left of
the formula bar
Type a one word name for the list, e.g. DateList
Press the Enter key.

Select cell X10
Choose Allow: List
In the formula box, type:
=OFFSET(DateList,MATCH(U10,DateList,0),0,115,1)
Click OK
Copy the validation cell down as far as required

Note: the dropdown list will only work if a year has been
entered in column U
 
You could put formulas in your Validation List instead of
values...........for example if A1 was your birth date,(say 1938) the top
of your validation list (say cell G1) could be =A1+1,(returns 1939) G2 could
be =G1+1,(returns 1940) G3 could be =G2+1, (returns 1941), etc
etc...........this would create the validation list with only those years
AFTER the subject was born............

Vaya con Dios,
Chuck, CABGx3
 
Back
Top