Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

1 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.

share|improve this answer
    
Thank you for the answer! I figured it would be tricky. It was to serve as a temporary task management sheet for a month and so if it's not easy to do then I'll just go the whole copy and paste route. No Biggie. Thanks again for your time. Cheers! –  Wendy Griffin Nov 18 '11 at 19:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.