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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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.
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!