Genius Level IF (?) Formula
Does such a formula exist to acheive the below?
If I edit the Stock columns in Row 1, I want the Result columns to appear as Row 2 (i.e. data shifted to the left eliminating blank cells).
I'm nearly there with this formula, but it's not quite correct.
=IF([Stock1]1 <> "", [Stock1]1, IF([Stock2]1 <> "", [Stock2]1, IF([Stock3]1 <> "", [Stock3]1, IF([Stock4]1 <> "", [Stock4]1, IF([Stock5]1 <> "", [Stock5]1, IF([Stock6]1 <> "", [Stock6]1, IF([Stock7]1 <> "", [Stock7]1)))))))
Comments

There are several ways to solve this problem, but i would avoid if statements. They tend to get a little complicated. instead try an index collect =iferror(index(collect([Stock1]1:[Stock7]1,[Stock1]1:[Stock7]1,not(isblank(@cell))),1),"")
And change the last 1 in the formula to account for which return you want. So it should be 17 as you go through the results
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives