Nested IF
This seems like a pretty simple nested IF statement but it's not ever hitting the last condition. Any insight would be helpful.
I'm autogenerating an Asset #. I want the formula to look at the Asset Type and if it's a "Video" insert a "V" and the autogenerated number (this works), if there is something in the Suite cell, put that and the autogenerated number, If not put what's in State and the autogenerated number.
What am I missing?
=IF([Asset Type]@row = "Video", "V" + [AutoNumber]@row, IF(Suite@row <> " ", Suite@row + [AutoNumber]@row, (State@row + [AutoNumber]@row)))
Thanks!
Best Answer

No, I wanted to make sure we didn't need to add text to it to force it to text.
I deleted the formula from the 2nd IF and rebuilt it again. Perhaps there was an inadvertent space we were not seeing but the formula is now working on my test sheet
=IF([Asset Type]@row = "Video", "V" + [AutoNumber]@row, IF(Suite@row <> "", Suite@row + [AutoNumber]@row, State@row + [AutoNumber]@row))
Answers

Hey @Jeana
The formula above has a leading parenthesis prematurely closing off your last term. Try this
=IF([Asset Type]@row = "Video", "V" + [AutoNumber]@row, IF(Suite@row <> " ", Suite@row + [AutoNumber]@row, State@row + [AutoNumber]@row))
Does that work for you?
Kelly

Appreciate the response Kelly but that didn't work. When the last condition is met I still only get the auto generated number and not the state code in front of it. The other conditions work fine in testing.
Any other thoughts?
Jeana

Hey Jeana
Is your state code a text string, eg. NY, or is it a number?

It's a value (NV), good point. Do I need a VALUE statement in here? The Suite is also a Value and it's working.

No, I wanted to make sure we didn't need to add text to it to force it to text.
I deleted the formula from the 2nd IF and rebuilt it again. Perhaps there was an inadvertent space we were not seeing but the formula is now working on my test sheet
=IF([Asset Type]@row = "Video", "V" + [AutoNumber]@row, IF(Suite@row <> "", Suite@row + [AutoNumber]@row, State@row + [AutoNumber]@row))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!