=IFERROR(INDEX(DISTINCT) returning #UNPARSEABLE

GrantD

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!

Paul Newcome

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).

Paul Newcome

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).

GrantD

@Paul Newcome

Hi Paul,

Thank you for taking the time to respond to my post. Your solution worked perfectly. :)

G.

Paul Newcome

Happy to help. 👍️