Can I add a formula to a drop down list cell?

Kyle Sullivan

I currently have drop down list that contains, "Not Started", "In Progress", and "Complete". What I would like to do is add a formula that automatically changes the drop down list from "Not Started" to "In Progress" if the Duration cell has been changed from 0% to anything non-zero. 

Is it possible to add a formula to a drop down list cell?

Mike Wilday

You can add a formula to a dropdown cell... HOWEVER, if you use that in a report that you expect someone to adjust, be aware that fields with formulas cannot be adjusted in reports.

To test this, create a new drop-down column and place your formula in it... =IF[Duration]3<0,"In Progress", "Not Started"

(assuming you are using this in row 3)

View your report, make sure that new dropdown column is added and try to change the dropdown in the report - you will not have access to change the field. Formulas lock it down. 

J. Craig Williams

Kyle,

A few points:

1. You are using the standard Duration column, the value should not be in percentage. You may be thinking of the [% Complete] column.

2. Mike alludes to the concept that if you are using a formula, you can not allow/expect the users to update the column directly. To that end, having a formula looking at (what I believe to be) the [% Complete] column is a good start, but you must make sure you account for all the status' that you wish.

3. Lastly, because the column is being updated via a formula, making it a drop-down type column is redundant and not needed UNLESS you using the same column in a different sheet that is updated manually and you want to see both columns (as one) on the same report(s).

All that said, this is the formula I would recommend:

=IF([% Complete]23 = 1, "Compete", IF([% Complete]23 = 0, "Not Started", "In Progress"))

Last note: =1 is used here because a cell formatted as percentage is 1=100%

I hope this helps.

Craig