Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

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

Formulas in drop down lists
 

PS: I'm using Office 2007.

Formulas in drop down lists
 

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

Formulas in drop down lists
 

You're welcome! Glad you got it working.

There's a limit to what you can enter in the Source box of the Data
Validation dialog box, so it's usually easier to refer to a list on a
worksheet, or a named range. In this case, the delimited list works better.

The separator that you use will depend on your regional settings. Here
in English Canada it's a comma, and in other places it's a semi-colon.

Buckie wrote:
SUPERB! It works (I used method 2). The clue apparently is not to use a
column with values to define your drop down list (which is the standard way
proposed by excell "help" :-s ) but to add the values directly in the
"source" box (I did not know this was possible, is much easier). Just one
remark for others trying this: you have to seperate the list options with ;
not with , .

Thanks very much Debra!!

"Debra Dalgleish" wrote:


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




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