Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas in drop down lists
PS: I'm using Office 2007. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
drop down lists and formulas | Excel Discussion (Misc queries) | |||
Drop down lists that auto create and then filter the next drop down list | Excel Worksheet Functions | |||
Conditional Formulas that return different drop down lists | Excel Discussion (Misc queries) | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
formulas and drop down lists | Excel Worksheet Functions |