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
