Referring to Dropdown value in a formula

Lisa Doyle

Is there a way to set the value of a dropdown in Column A from a formula in Column B? This is my formula; 

    Column A = Status3

    Column B = Expiration Date

 

=IF([Expiration Date]3 > TODAY(), Status3 = "In Force", Status3 = "Pending")

 

It gives me #INVALID COLUMN VALUE error message in Column B

 

Any help would be appreciated.

J. Craig Williams

Lisa,

 

1. Is your column A named [Status] or [Status3]? 

- I assume Status.

 

2. You can't use a formula in one cell to set the value in another cell.

 

In your [Status] column, row 3, this would get the value from the date column:

 

=IF([Expiration Date]3 > TODAY(), "In Force", "Pending")

 

Note that if someone uses the dropdown list (or types) a value, the formula will be overwritten and gone.

If you are going to be using Status as a formula, then the dropdown list is just asking for trouble. Better is to change it to a Text/Number (though it really doesn't matter much) and Lock the column so non-Admins can't mess with your formulas. They like to do that.

 

Craig