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?

Find more posts tagged with

Education

Formulas

Conditional Formatting

Project Management

Cell linking

Comments

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

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