Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto fill cells (excel 2003)

    I have a cell in column A where the end user will choose a department from a list. There are 3 digit codes associated with each department and I want the corresponding 3 digit code to automatically populate a cell in column B.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto fill cells (excel 2003)

    Create a table somewhere in your workbook with the departments in the first column and the 3-digit codes in the second column (the first column could be the source for the dropdown list). Let's say that this table is in P2:Q12.
    With a department in A4, the following formula in B4 will display the corresponding code:

    =VLOOKUP(A4, $P$2:$Q$12, 2, FALSE)

    The table can also be on another sheet:

    =VLOOKUP(A4, 'Other Sheet'!$P$2:$Q$12, 2, FALSE)

  3. #3
    Lounger
    Join Date
    Dec 2007
    Location
    Sacramento, California, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: auto fill cells (excel 2003)

    Worked perfectly, of course!!

    I am getting a #N/A error in the fields where the formula is but no department has been chosen. Is there a way to just show those fields as blank until a department is chosen. I want to have the formula already filled in to the empty cells.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: auto fill cells (excel 2003)

    Using the same example as in my previous reply:
    <code>
    =IF(ISNA(VLOOKUP(A4, $P$2:$Q$12, 2, FALSE)), "" ,VLOOKUP(A4, $P$2:$Q$12, 2, FALSE))</code>

Posting Permissions

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