=IFERROR(INDEX(DISTINCT) returning #UNPARSEABLE

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

where:

{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), "")

where:

[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!

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    When using a cross sheet reference, you do not need to have it repeated like you do in that first one.

    {Range}:{Range}

    is incorrect.

    {Range}

    is the way you want to go (like you did in the second instance).

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!