INDEX(DISTINCT(COLLECT Results in INVALID DATA TYPE Error

Options

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.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!