There are a couple of ways to do this. Let's assume that you can sort the data on VendorInfo by Vendor name.
On VendorInfo, you will define a dynamic named range (this will allow you to add to the Vendor Names on VendorInfo without changing your formula). With VendorInfo as the active sheet, go to Insert > Name > Define. In the Name box, type: VendorData. Then in the refers to box, paste this (if you copy, be sure to get rid of the space at the end that will inevitably appear):
Click Add. Now to check that it really refers to your data, Click inside the formula, and the data range will be automatically selected with the "moving ants" (<smile>). Click OK.
Now go to PurchaseOrders, put the following in the respective cells:
The only thing that changes is the number. If you have more columns in VendorInfo, then increase the number in the formula (column reference - how many columns over from the first, be sure to include the first column)
Now, when you type in the Vendor Name in C5, these cells will automatically populate.
Will the Vendor Names only appear once in VendorInfo? If so, then go back to VendorInfo and add another named range. In the Name box, type VendorName, and in the Refers to box, put this:
Click Add. This will now give you only the names of Vendors (not the heading).
Now back on PurchaseOrders, select cell C5, go to Data > Validation, and in the Allow dropdown, select List. In the resulting Source box, type
Now the drop down box will give you all the names of vendors on VendorInfo.
If you can't sort the Vendors in VendorInfo, then we will have to expand the lookup by using Index-Match. And if there are duplicates of the Vendors names on VendorInfo, we will have to set up another sheet (good practice anyway) using Advanced Filter to get only unique names, and then define our VendorName based on that list.
Last edited by shades; 11-06-04 at 00:26.
old, slow, and confused
but at least I'm inconsistent!