There are two separate solutions that I suggested. The cells with the
drop down lists should have General or Number format.

1) In ToolsOptions, change the reference style to R1C1
Select the cells where you want the data validation drop down
Choose DataValidation
Allow: List
Source: Deceased, Retired, =TODAY()-RC3

2) In ToolsOptions, turn off the R1C1 reference style
Select a cell in row 2
Choose InsertNameDefine
Name: Age
Refers To: =YEAR(TODAY())-YEAR(Sheet1!$C2)
(use your sheet name instead of Sheet1)
Click OK
Select the cells where you want the data validation drop down
Choose DataValidation
Allow: List
Source: Deceased, Retired, =Age

Buckie wrote:
I'm sorry, but could you please explain in more detail? I can't get it to
work here. R1C1 does not make a difference, and if I use a mixed reference,
the options in the drop down list are not given as "=Age", but it shows the
calculated value that is in the column that defines the drop down list.
Changing the cell format to text so the drop down list displays =Age does not
work, since it keeps the "text" format and is not recognized as a formula
when I select it from the drop down menu.

Thanks for your help.

(By now I'm just very curious if this is possible)

"TomPl" wrote:


That works!

Very nicely done.

"Debra Dalgleish" wrote:


Is the formula in the drop down list, or is Age a defined name?

If the formula's in the drop down list, it could work if you use R1C1
referencing (ToolsOptions, General tab)

Or, without R1C1, you can define Age with a mixed reference.
For example, select a cell in row 2
Then, choose InsertNameDefine, and enter Age as the Name.
For the Refers to, enter =YEAR(TODAY())-YEAR(Sheet1!$C2)
Click OK

In the drop down list, the options would be:
Deceased, Retired, =Age

Buckie wrote:

Thanks, interesting. But it is not what I was trying to do. My goal is to
make a drop down menu with 3 options: 1) Deceased, 2) Retired, 3) Age

Now 1) and 2) are not dates, they are fixed. They are the terms "deceased"
or "retired".

3) is Age and is dependent on (a) the person (defined in the rows, each row
is different person) and (b) the difference between the current date
(TODAY()) and the birthday date (defined in a column, and obviously also
different per row/person). Age thus is no fixed value, but a number, changing
per row.

You can define a formula for an item in the drop down menu. In this example
it would look as: =YEAR(TODAY())-YEAR($C1) . Alright, this gives an age nr in
years in the drop down menu. But, if I go from row 1 to row 2, the formula in
the drop down menu does not change. It does not become
=YEAR(TODAY())-YEAR($C2).

So: it is in any way possible to make the items that appear in the drop down
menu dynamic (so the formula changes, as it would when just working in the
sheet and expanding the formula range over more cells).



"Bob Phillips" wrote:



Should be simple

=TODAY()-IF(COLUMN()=3,birth_date,IF(COLUMN()=5,retirement_ date,date_deceeased))

--
__________________________________
HTH

Bob

"Buckie" wrote in message
...


Hey,
is it possible to include a formula in a drop down list and make the value
that appears in the drop down list dependent on de row/column your in.

To be more precise: I'm doing a study and have a list of people with
different birth dates. One column is "age". I can calculate this with the
formula =TODAY()-"Birth daye cell nr". Now, of course this is different
for
each row (person). I want to make a drop down list with the options: Age
(calculated) - Retired - Deceased. I've entered the formula in a drop down
list, but the formula does not change with the rows, so I end up with a
fixed
age value.

Is it possible to make it variable, depending on the row (person)??




--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com