Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92

    Unanswered: Lookup function question

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    Last edited by shades; 11-06-04 at 00:26.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    Thank you very much shades. This answered my question perfectly, and solved it perfectly as well!!!

    I do appreciate the help!

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Great! Glad to help. And thanks for coming back to let us know the solution worked for you.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2003
    Location
    Atlanta, GA
    Posts
    92
    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.
    Last edited by inzzane; 11-07-04 at 02:27.

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    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
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •