Forum Discussion
DPRC512
May 11, 2022Copper Contributor
IF statement based on dropdown list help
I have a dropdown list of product names and want to create an IF formula in another column to populate the experimental product number associated with the product name. So select the product from the list, the neighboring cell then populates the experimental number.
I keep getting an error and I'm not sure why.
Example:
=IF(B2="productX",ABC-123, IF(B2="productY",DEF-456))
Can you not create an IF statement from a cell with a dropdown list?
quick answer if I'm pretty sure your problem is the 'shortened' result is TEXT and not a number. Simply add = --( original formula ) to force excel to convert it into a number or use VALUE( orig formula). So in your example it would be:
=--(LEFT(E12,4)&RIGHT(E12,LEN(E12)-6))
FYI - another thought (not related to the actual problem) is you could use SUBSTITUTE() to replace "-0" with "" to remove those 2 characters instead of getting LEFT and RIGHT and putting them back together so again in your example it would be:=--SUBSTITUTE(E12,"-0","")
2 Replies
Sort By
- OliverScheurichGold Contributor
=IF(B2="productX","ABC-123",IF(B2="productY","DEF-456"))
Does it work if you enter the results with " " ?
- DPRC512Copper ContributorYes! Silly quotes. Thank you!