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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!