I have 2 sheets in my workbook (PurchaseOrders and VendorInfo).
In VendorInfo, I have all of my vendors and their addresses.
A=Vendor Name
B=Address1
C=Address2
D=City
etc..

In Purchase Orders I have:
B5=Vendor Name
B6=Address1
B7=Address2
etc...

What I am trying to figure out how to do is set it to where I can select a vendor from a list on PurchaseOrders/VendorName, and have it fill out the rest of the fields on that sheet for me.

I tried to create a validate list, but it will only work if the list is on the same form. How would I be able to go about creating these fields if the information is not on the same form?

Or can you direct me to a good article on how to do this?

Thanks.

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):

=OFFSET(VendorInfo!$A$1,0,0,COUNTA(VendorInfo!$A:$ A),COUNTA(VendorInfo!$1:$1))

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:

C6

=VLOOKUP($C$5,VendorData,2,)

C7

=VLOOKUP($C$5,VendorData,3,)

C8

=VLOOKUP($C$5,VendorData,4,)

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:

=OFFSET(VendorInfo!$A$2,0,0,COUNTA(VendorInfo!$A:$ A)-1,1)

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

=VendorName

Click OK.

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.

Thank you very much shades. This answered my question perfectly, and solved it perfectly as well!!!

I do appreciate the help!

Great! Glad to help. And thanks for coming back to let us know the solution worked for you.

No Problem. I've searched many dead end threads and know how it feels to try many unresponded, and wrong answers.

I do however have another question, which is somewhat related.

In PurchaseOrders C6 I have the name of the company.

In Purchase Orders:
C28=Item #
D28=Description
F28=Cost


On Sheet, StockInfo, I have:
A=VendorName, B=Item#, C=Description, D=Price

Is there any way I can u8se this to my advantage? I mean I know I can with access, but not sure about excel.

What I was thinking was of a way once the Vendor is chosen, to be able to select/create a list of items descriptions only from that vendor, and have it fill out the order.

I'm sure I can figre out how to autofill the reports from the first question, but how would one go about creating a list like this in excel if possible?

(I recently got chewwed out by my boss because my purchasing rep kept messing up the item numbers and addresses on orders recently, and figure this would solve the problem)

Any advice on this, please let me know.

If I understand you correctly, try this solution by Aladin Akyurek. He is probably the premier formula expert in the world.

http://www.mrexcel.com/board2/viewto...ent+validation