Creating Index for Unique Values
I am trying to create a formula that can be converted to a column formula but am having issues. I referred to https://community.smartsheet.com/discussion/54816/extractingalistofuniquevaluesfromalistonanothersheet to come up with the formula to use, however it uses absolute references, which can't be made into column formulas.
What do I need to modify to allow this to work and become a column wide formula? My formula is:
=IF([Project Sum Hierarchy Helper]@row = 1, "", (IF(COUNTIFS([Job Name]$1:[Job Name]@row, [Job Name]@row) = 1, COUNTIFS([Job Name]$1:[Job Name]@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell)))))))
I added a bit to ignore parent rows, as the data I am trying to index are always child rows.
Best Answer

Taking a look at the original thread/solution, this type of formula requires the absolute reference to create the Range in which to identify the number for that row. It has to be locked to row $1 in order for this formula to calculate properly. This means that, as you've found, this formula is not able to be set as a Column Formula, nor can I think of an alternate solution to return the same result without the use of an absolute reference.
That said, formulas in Smartsheet will autopopulate as new rows are added in. As long as two rows above have the same formula, the next row created/inserted will automatically fill the formula into that row's cell as well (see: Use or Override Automatic Formatting and Formula Autofill).
You could add this formula into your column, dragfill it down to fill all current rows, and then lock the column so editors can't delete the formula. As long as new rows are added directly beneath current content (versus after a few blank rows) your formula will continue to populate, similar to a Column Formula. Will this work for you?
Cheers,
Genevieve
Answers

Taking a look at the original thread/solution, this type of formula requires the absolute reference to create the Range in which to identify the number for that row. It has to be locked to row $1 in order for this formula to calculate properly. This means that, as you've found, this formula is not able to be set as a Column Formula, nor can I think of an alternate solution to return the same result without the use of an absolute reference.
That said, formulas in Smartsheet will autopopulate as new rows are added in. As long as two rows above have the same formula, the next row created/inserted will automatically fill the formula into that row's cell as well (see: Use or Override Automatic Formatting and Formula Autofill).
You could add this formula into your column, dragfill it down to fill all current rows, and then lock the column so editors can't delete the formula. As long as new rows are added directly beneath current content (versus after a few blank rows) your formula will continue to populate, similar to a Column Formula. Will this work for you?
Cheers,
Genevieve

I believe that will work. Will adding rows in say, the middle of the sheet cause problems? Or as long as there are at least two rows above it, it will populate?
EDIT:
Never mind. After reading the article you posted, it appears that it will still work even if inserted as long as there is content above and below it. This should work then!

Adding rows between other rows won't be a problem at all! As long as the formula is in the row above and the row below.
Here is the list of conditions in order for a formula to populate automatically:
 At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
 Above or below a single row that is between blank rows and has formulas.
 Directly between two others that contain the same formula in adjacent cells.
This list is outlined in the Help Article for Autofill, here.
Cheers,
Genevieve

Genevieve, now I am having problems implementing the second part of Paul's solution in the linked post. I don't follow how he created the list of names from the helper column. Any chance you could provide a bit of insight?

In @Paul Newcome's second screen capture he has the second formula listed on the left:
=IFERROR(INDEX({Name List}, SMALL({Row ID}, [Column2]@row)), "")
Is this where you're having trouble?

It was. I apparently just needed a little more coffee as I have figured it out. Thanks for checking back!

Haha no problem! Glad you got it working. 🙂

The linked solution was actually precolumn formula. To modify everything to allow column formulas, we will need to insert two helper columns and adjust the formulas only a bit.
The first helper column is a system generated autonumber column with no special formatting required. I'll call this column "Auto".
Next we need a text/number column that I'll call "Row", and we will use this column formula:
=MATCH(Auto@row, Auto:Auto, 0)
Now that we can leverage our row numbers, we can update the first COUNTIFS to remove the specified rows in the first range so that it is looking at the entire column and then add in a range/criteria set of row numbers that are less than or equal to the current row number. The second COUNTIFS is outputting the row number, but since we already have that on the sheet now, we can replace it with a basic cell reference to get rid of the redundancy.
That gives us a final column formula of:
=IF([Project Sum Hierarchy Helper]@row = 1, "", IF(COUNTIFS([Job Name]:[Job Name]@row, [Job Name]@row, Row:Row, @cell <= Row@row) = 1, Row@row)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
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