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.

Auto-populate a value based on dropdown

edited 12/09/19 in Archived 2017 Posts

I am trying to have a column with dropdown choices auto-populate a value in the next column. If IP-A is chosen in the AssetType column, the TotalHours column should display 1. If IP-D is chosen the TotalHours should display 15.

I was thinking conditional formatting would work but it only formats text/background. Is this possible at all?



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You would have to create a nested IF statement. And put it into your Total columns. Adjust the 1 after asset type for the row number you are putting the formula into and then auto-drag the formulas into the rest of the rows. 

    =IF(AssetType1 ="IP-A", 1, IF(AssetType1="IP-B", 2, IF(AssetType1="IP-C", 10, IF(AssetType1="IP-D", 15, IF(AssetType1="VID-A", 4, IF(AssetType1="VID-B", 8, IF(AssetType1="VID-C", 17")))))))

    Hope that helps! 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    If possible*, I would NOT go with the NestedIF solution here.

    I would set up a table area (if you have a header or footer area, then there, otherwise in an unused portion of your sheet - even if you need to create one of those) and then use the LOOKUP() function.

    This NestedIF is pretty small, but takes more processing (I believe) than a LOOKUP of comparable size. Easier to update. Easier to expand. Easier to see logic from a user perspective.

    *by possible I mean there are lots of design considerations that also may impact one solution vs another and I don't have all the facts. My general rule of thumb for a straight NestedIF like this (one criteria) is 4 or less = NestedIF, 5 or more = LOOKUP.




This discussion has been closed.