Hi Smartsheet Community

I have a rather complicated set up with this one. I have a source sheet with the following columns:

[Full Name], [Month, KES Number], [Classroom], and a helper column ([Month]+[KES Number]+[Classroom]). I'm using the helper column to identify unique instances.

On the target sheet, I have:

[Full Name], [Month], [KES Number] and [Classroom], among many other cells. I would like to populate Full Name, Month, and Classroom for each unique combination of the helper column in the source sheet ([Month]+[KES Number]+[Classroom]).

Because individuals can report for multiple classrooms on a given month, I can't just pre-populate those fields of row data.

The formula that I'm trying to use is:

=IFERROR(INDEX(DISTINCT({PAR Submissions Range 8}:{PAR Submissions Range 8}), {PAR Submissions Range 9}, 1), "")


{Par Submissions Range 8} is the helper column (source sheet)

PAR Submissions Range 9 is an Auto-Number column (1,2,3...) (source sheet)

I have also tried:

=IFERROR(INDEX(DISTINCT({PAR Submissions Range 8}:{PAR Submissions Range 8}), [Auto-Number]@row}, 1), "")


[Auto-Number] is an auto-number column in the target sheet

I keep getting the #UNPARSEABLE error. I'll admit the syntax is getting a little confusing for me here, but I think that this is very possible. Any assistance or advice would be greatly appreciated. Thanks so much, in advance!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!