Pulling Data From Database Or Other Excel workbook

[Greg101]

I need to create a couple of costing spreadsheets. I want them separate for a few reasons. They will all source data from a central list of about 1,000 items. These items contain an item name, and price per unit.

I also need to use Data Validation in my costing sheets. I have a few problems to solve.

1) My central database of items changes often. But because data validation can not source from outside of a workbook (a silly limitation IMHO) I want to create a replica of that database in each workbook on a separate sheet. Then have the formulas in my costing models pull from that list using VLOOKUP. However, my users will not be able to constantly update the master list that is in each workbook to make sure it matches the information in the central list. How can I set this up so that the replica of the central list in each workbook automatically updates itself with the latest information from the central list?

2) In my data validation fields, I have about 1,000 items. Our items, unfortunately, have many different ways of being spelled, named, so a list is the only way to ensure proper item entry. However, as you may have experienced, those drop down lists take FOREVER to scroll through. Is there any way to implement an autocomplete? I have had a lot of trouble with this.

Thanks.
Greg

[Greg101]

Anyone have any ideas?

[Kyle123]

It sounds like a database would be a better choice than Excel - do you have Access?

[Greg101]

It sounds like a database would be a better choice than Excel - do you have Access?

I don't but I was thinking that I would probably get this as a suggestion. Are there any good open source data bases out there that work with excel?

Thanks for your response.