Archived 2017 Posts
Archived 2017 Posts
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
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?
Comments
-
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!
-
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.
Craig
