Index - Distinct returning an error

K Lansdell

I am using this formula to pull distinct entries from another sheet:

=IFERROR(INDEX(DISTINCT({Linked Sheet Range 11}), $IDX@row), "")

The entries are returning as blank - so indicating an error for the whole column. I have used the same formula in the sheet for other data ranges in the same source sheet and it's working fine.

The data in the source sheet is a list of projects, formatted as text / number (example below).

Any thoughts on what is causing this error?

Kelly Moore

Hey @K Lansdell

What error are you receiving when you remove the IFERROR? The type of error points towards the issue.

What type of data is in the IDX column? The Index function is expecting a number to indicate where, in the list it just gathered, is the exact data being requested

https://help.smartsheet.com/function/index

Kelly

K Lansdell

IDX column - just a list of numbers 1 through 500

Without the IFERROR - the error being returned is: #INVALID DATA TYPE

K Lansdell

I am using the exact same formula - just pulling from another column in the same source sheet, same IDX column with no issues - hence being perplexed!

Kelly Moore

@K Lansdell

Are all the values in the {Linked Sheet Range 11} the same data type? That is all numbers, or all textstrings. Or, no existing errors in that column?

Distinct can only be used on one data type at a time. Often you can tell textstrings from numbers by the way that smartsheet justifies it in a column. True numbers will be right justified. Textstrings (even if they appear like a number to us) will be left justified. Any cells with errors in the column can crash a distinct

K Lansdell

There is a mix of entries - some numbers, some text, some text and numbers (these are all protocol references so included as per the original client detail). Checking in a non-formatted sheet some are textstrings and some true numbers. No existing errors as far as I can see (source data is pasted in from external systems).

Format of the column is text / number, but there is no way to segregate - as they all belong in the column. Unless there is a formula I can use to separate into 2 columns to start with!

Kelly Moore

Hey @K Lansdell

Could you add a helper column in the source sheet to convert all values to text? Then reference the helper column for your distinct formula.

The helper column (on the source sheet) would have this formula

=IF(ISNUMBER([whatever your column name is]@row), [whatever your column name is]@row+"",[whatever your column name is]@row)

Will this work for you?
Kelly

K Lansdell

Thanks @Kelly Moore - this was where my thinking was heading. Will try the helper column. Have got it to work by changing the source column to a contact list format (even though it isn't a list of contacts). Will try the helper column as a neater solution.