Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Referring to Dropdown value in a formula

Lisa Doyle
edited 12/09/19 in Archived 2016 Posts

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
    J. Craig Williams ✭✭✭✭✭✭



    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.




This discussion has been closed.