I am working with excel that has a sheet with significant amount of data. 229 000 rows to be exact. The problem is, data validation cannot analyse such great amounts of information and when I try to make drop down lists, they are always incomplete. I wanted the dropdown lists to work as an autofill for names of companies.

Sheet1 is where I have the data validation cell Sheet2 is where I keep all the data on company names and other information

I thought I could solve this by writing and executing macros, but our company doesn't allow macros to be enabled, which makes things bit more difficult.

I am working with excel that has a sheet with significant amount of data. 229 000 rows to be exact. The problem is, data validation cannot analyse such great amounts of information and when I try to make drop down lists, they are always incomplete. I wanted the dropdown lists to work as an autofill for names of companies.

Sheet1 is where I have the data validation cell Sheet2 is where I keep all the data on company names and other information

I thought I could solve this by writing and executing macros, but our company doesn't allow macros to be enabled, which makes things bit more difficult.

• excel

• 1 If the data has categories, you might try using dependent validation lists? – jkpieterse Commented Jan 21 at 13:25

1 Answer 1

Why don't you try to maintain a list of unique values and make your drop down off of that list, instead of linking your drop down to the entire dataset. You need to keep the reference list updated after each data refresh.

Option A: After each update, just take the column with company names, drop it into a new sheet and use data->remove duplicates.

Option B: Use a dynamic array to always keep a list of unique values, for example =UNIQUE(data!U:U)