Results 1 to 7 of 7

Thread: Autofill data

  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    Don't know if this can be done in Excel but I thought I'd ask anyway.

    The Corporate office has given us a spreadsheet that we use to submit our AP. Column D is used for a Vendor Code, Column E is used for Vendor Name and Column I is used for Customer Number. What I've done is list all of the Vendor Codes and Names a few dozen lines below the print area so that it autofills at least. That's been a little timesaver.

    My question is this: is there any way that when I enter a Vendor Code, the name and number would automatically fill in as well? Even if I add more vendors in the list?

    Thank you!
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='weese237' post='772100' date='24-Apr-2009 02:23']My question is this: is there any way that when I enter a Vendor Code, the name and number would automatically fill in as well? Even if I add more vendors in the list?[/quote]
    You could use VLOOKUP for this.
    Create a list (maybe on a different worksheet) that has all the vendor codes in one column, the vendor names in the next column and the vendor numbers in the third column. Give this range a name, such as VendorTable.
    If the user enters a vendor code in cell D4 then you could have the following formulae in E4 and F4
    =VLOOKUP(D4,VendorTable,2,False)
    =VLOOKUP(D4,VendorTable,3,False)

    You can drag the formulae in E4 and F4 down to fill the rows below, but with the formula I have given you this would fill all these cells with #NA because row D doesn't have any customer short codes yet. To prevent this you could use formulae like
    =IF(ISNA(VLOOKUP(D4,VendorTable,2,FALSE)),"",VLOOK UP(D4,VendorTable,2,FALSE))
    =IF(ISNA(VLOOKUP(D4,VendorTable,3,FALSE)),"",VLOOK UP(D4,VendorTable,3,FALSE))
    which check for the ISNA error and replace it with an empty string.

    See attached example.

    [attachment=83448:Vlookup_Example.xls]
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To expand a bit on Stuart's solution, you could make the VendorTable a dynamic range that expands and shrinks automatically if vendors are added to the list.

    Also, you could use Data | Validation to show a dropdown list of vendor codes in column D. This again uses a dynamic named range.

    See the attached version of Stuart's workbook: [attachment=83449:Vlookup_Example.xls]
    See Excel -- Names -- Naming Ranges for info on dynamic ranges.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you gentlemen for your replies. Will take a closer look this weekend. I'm sure I'll have more questions.
    Louise

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello again,

    Was able to update my spreadsheet and it worked beautifully.

    I had another question/thought; The vendor codes are mostly 6 characters so on the dropdown list, (which is currently about 200 rows) I was wondering if there was a setting that I could use so that when I started typing the code, say the first 3 characters, I could click on the down arrow and the list would start from that point. Currently I get the error message and it starts from the top.

    Just wondering. It still works great!

    Thanks!
    Louise

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Unfortunately, Validation doesn't work that way. You could use a dropdown from the Control Toolbox instead, but that requires some advanced VBA code and disables the undo capability.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    That's Ok. Thanks again.
    Louise

Posting Permissions

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