Populate a dropdown field from another sheet.

**mjhopler** · 03-13-2011, 12:59 PM

Goal:
I want to auto-populate a field with data from another sheet, but the kicker is the field I want to populate has a dropdown and I want to keep this.

Problem(s):
1) The field being populated has a drop down. Normally I would use a simple VLookup to bring the data in, but the dropdown limits this.
2) I want to keep the dropdown. I want to keep the ability for people to change the populated data, but also want to restrict their options.
3) Have very limited experience with Macros (VBA). Is this going to require VBA?

**scottylad2** · 03-13-2011, 02:34 PM

If your dropdowns in sheet 1 A1, and you list is in Sheet 2 Highlight the Range in Sheet 2 that contains what you want in the list, in the name box give the list a name, List will do, then in A1 Sheet 1 press Alt then D L to get the data validation dialogue box, select list from the drop down then click in the Source box and hit F3, paste the name List then click ok. Your Dropdown should now function with the contents of the list in sheet 2

**mjhopler** · 03-13-2011, 03:35 PM

If I understand that correct, this will provide a dropdown option with the same dropdown form the first sheet, which I have. However, my problem is I have a data tape on one sheet which each row has specific data for a specific loan.On the second sheet, I am creating a summary report on each row.

To eliminate double work, rather than manually filling in each data point on the second sheet, I need it to find the related record from the data tape (the first sheet) and auto pull the data into this field on the second sheet while at the same time keeping the function of a dropdown in case the data is to be changed later.

Does that make sense?

**scottylad2** · 03-13-2011, 04:06 PM

You might get a clearer description if you post a sample workbook with similar layouts and you're expected results.

Far better you upload a sample book, trying to put your descriptions into pictures is not happening, it's Sunday evening!

reply using new post and look for the upload icon

**mjhopler** · 03-13-2011, 04:28 PM

Thank, I figured it out. Write the formula and then put the data validation in. The formula is deleted if data is changed, but that is ok, at least if pulls it in to begin with.

thank you