Excel sheet very slow while using data validation

[miicker]

Hello everyone,

I have a question, I have a worksheet which takes a very long time to load some values aftger using a dropdown menu created with data validation.

I've includes the worksheet. I am Dutch, so I just translated some thing to let it make more sence to you guys.

I've done the following thing:
In the tab "order" from A2:A510, it shows all the order numbers from the tab "Gegevens", this tab contains all information inserted from a database.
Form A511:A1000 in the tab "Orders" it shows all the Orders which are already chosen in the tab "planning"

Than in the tab "order" Starting at cell B2, i used this formula to look at all the ordernumbers from A2:A1000 to only pick the unique ones
This is the formula:

=IFERROR(INDEX(A$2:A$1000;MATCH(SMALL(IF(A$2:A$1000<>"";IF(COUNTIF(A$2:A$1000;A$2:A$1000)=1;A$2:A$1000));ROWS(B$2:B2));A$2:A$1000;0));"")

which works pretty well.

Then I used data validation to let users select a order number from that range of values, so they can only select items which they haven't selected before.

This all works pretty well, but is verry, verry slow.

I hope I have explained this well and someone can help me.

Kind regards,
miicker

This is the worksheet
https://www.dropbox.com/s/mnrv0y3rrz67xdz/Test.xlsm

 

[miicker]

I removed a lot of the array formulas but it is still slow, does someone has an advice or solution?