INDEX(DISTINCT(COLLECT Results in INVALID DATA TYPE Error

Christian Graf

I have a column formula that is designed to pull only distinct values from a column in another sheet. However, the function is throwing an INVALID DATA TYPE Error.

Sheets involved:

• Stock sheet
• Order sheet

References:

• {Part Number (SCMI)} - A reference originating in the Order sheet, pointing to the Stock sheet part number column.

Columns:

• [Line ID] a column in the Order sheet. Created as an auto number column type.
• [Row ID] a column in the Order sheet. Created using the helper column [Line ID] with the following formula: =MATCH([Line ID]@row, [Line ID]:[Line ID], 0).
• [Part Number] a column in the Stock sheet. Entries are manually entered 6 digit numbers.

The function in the Order sheet part number column:

=INDEX(DISTINCT(COLLECT({Part Number (SCMI)}, {Part Number (SCMI)}, <>"")), [Row ID]@row)

Current Output: INVALID DATA TYPE

Desired Output: Automatically Populated List of Unique Part Numbers in the Order sheet from the Stock sheet.

Christian Graf

I was able to solve it by adding the helper columns [Row ID] and [Line ID] to the stock sheet as well.

Christian Graf

I was able to solve it by adding the helper columns [Row ID] and [Line ID] to the stock sheet as well.