Yes, that is another solution. I also thought about something similer around
the problem, but nevertheless thanks for helping because your suggestion is
more "elegant". I was just wondering if "dynamic formulas" (or however you
want to call them) could be incorporated in drop down lists. It looks like
it's not possible.

"TomPl" wrote:

I don't think that what you are asking for can be done directly. If you
added a column, you could achieve something similar.

Column A would have data validation from a list "Age", "Deceased" or
"Retired".
Column B would have the formula =if(A2="Age",=YEAR(TODAY())-YEAR($C2),A2).
Column C would have the birth date.

Then you could use the dropdown list in column A and the result you want
would be in column B.

Be flexable.

Tom


"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)??