Obtaining a Unique List of Values for Data Validation

[a68tbird]

Hello All -
I have a document that has a table of vendors with the products they sell (and all relevant details for each product, ie. cost, SKU, etc). The table is a few hundred rows long, representing each product available to order. The Vendor column has many repeated entries, as each vendor has their multiple products. I'm trying to make it easy for the user to fill in an order form, using dropdown menus where possible. With Data Validation, List as type and using the formula

=INDIRECT("tblProducts[Vendor]")

I can get a unique list of the vendors - but only when using Excel online. Opening the workbook in the app, that list is not filtered for distinct entries. I'd like this list to only have unique values when using the app. I thought perhaps I could have a separate table that was just populated with the Vendor names, but UNIQUE in a table produces a #SPILL error. I want to use a table column for the List source so that it remains dynamic. If I were to create a list of vendors using the UNIQUE function just down a range instead of a table, then the List source would become out of sync if a new Vendor gets added to the Products table. That Source formula becomes

=INDIRECT("Sheet3!$A$1:$A$300")

- a static reference. I could use a Named Range, but then that named range has to be manipulated each time a new vendor is added to the list.

Any suggestions on creating a unique list for data validation?

Thanks very much.

 

[a68tbird]

My apologies - I apparently asked this exact question last summer! LOL. Please ignore the post.