Populating cells with data from another spreadsheet after just keying in a few letters

Question

I have 1 workbook with 2 spreadsheets. Spreadsheet 2 column A contains a long list of company names, Columns B - H contain critical information about the company.

Spreadsheet 1 contains all of the columns as Spreadsheet 2 plus some other columns. What I'm trying to achieve is that when you start to type in the first 3 characters of a company name on Spreadsheet 1 it would then have a drop down of the companies (as listed on Spreadsheet 2) that share the first 3-5 letters and you would select one. Upon selecting a company name all of the corresponding company information would populate in the other columns on spreadsheet 1 automatically. This is to avoid copying a row from Spreadsheet 2 and pasting it in Spreadsheet 1.

Any help with this would be greatly appreciated. Cheers!

Answer 1

Theres not an easy way of doing this aside from doing whole bunch of VBA programming (check out StackOverflow if you want to go that far).

The auto complete feature is doable by doing a data import from sheet sheet two into sheet 1 on colum XYZ and then hiding it. Once you import the data, the link between the two should be maintained untill one of them are move. The advantage is that if you update the list in sheet two, it will update in sheet 1 where the data was imported. On the column where you would like to autocomplete, you are going to want to data validation with a combobox where the referencing the imported data on sheet 1 (dont think you can reference a different sheet, but I could be wrong). This will in turn make autocomplete recomendations that exist in sheet 2.

As far as the completing the other columns with the associated information, theres no easy way in Excel (maybe lots of VLOOKUP's). I highly recommend Mircrosft Access as its built for that kind of functionality, but anticipate a relatively high learning curve. Theres also a good support page called Access Monster for Access.