INDEX consecutive row indexing
I've had this issue come up a bunch for me when trying to write formulas. Say I've got a column of Order Numbers that have a lot of repeated numbers in it. I can write a formula to pull out distinct numbers into another sheet such as this formula:
=INDEX(DISTINCT({Order Number}), 1)
However, that's only going to pull out the first one into my new sheet. What if I have over 100 distinct order numbers I want to pull into a new sheet? Currently I only know of one way to do it. By changing the row index number at the end to 2, then 3, then 4, and it can take an hour or more to put that formula into over 100 rows.
Is there a faster way to pull out the distinct numbers aside from manually telling SmartSheet to give me the first result, then the second, then the third, all manually?
Best Answer

You can insert a text/number column and then manually enter
1
2
From there you can dragfill down as far as you need to go and replace the hardcoded number in the formula with a cell reference to this new column.
Answers

Hi guys. Do either of you know of an easy solution for this type of referencing large lists?

You can insert a text/number column and then manually enter
1
2
From there you can dragfill down as far as you need to go and replace the hardcoded number in the formula with a cell reference to this new column.

Thanks Paul. I'll try that next time I have a need to pull in a long list like this. Yesterday I just did it manually.

Um...where's the option to mark a response as the answer. I'm not seeing that anywhere...

Hey @Mike TV
Thanks for the tag! It looks like you had originally posted this as a Discussion, which does not have accepted answers. We've changed this to be a Question type of post, so you should now see the "yes" and "no" at the bottom. 🙂
Cheers,
Genevieve

Thanks. I've only posted a few posts myself and didn't know there was a difference.

No problem! You found the difference 😎
Questions will appear in the "Unanswered" queue as well, whereas Discussions don't require a solution.

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 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!