Welcome!

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)))))))

Find more posts tagged with

Comments

L_123

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 1-7 as you go through the results

Sign in to join the conversation!

Quick Links

Trending discussions

How can I view the Gantt chart in days?
When I use the zoom-in option, I can only see the day names but not the specific day numbers. I need my team to be able to see the exact dates for the tasks I have scheduled.
How to create stack column graph bars
I'm trying to create a graph chart that captures monthly count of records by divisions (Northeast, West and Central) that should be stacked with a legend displaying each by different color like a presented in Excel. When I attempt to duplicate the process in Smartsheet I receive the following results. In Excel, I created…
Not enough space in the cell for my notes
Hi, I'm using smartsheet to track updates on each task in a tracker. These updates may span over a year, so they get lengthy. I was excited about smartsheets because viewing these notes is a lot cleaner. However, I've just realized that there is a content limit to a cell, which is a huge issue. While excel was annoying to…