Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is it possible in excel to have a cell value automatically inserted once the previous cell has been inserted, where the previous cell has a direct relationship to this one which is defined in another table.

For example: on the first worksheet there is 2 columns: 'isbn' and 'book title', on another worksheet there is a table containing isbn values and there corresponding book titles. On the first worksheet the isbn column is a drop down that lists the isbn's from the table on worksheet2.

When the user selects a isbn from the dropdown i would like the book title column to be filled in automatically with the corresponding title from worksheet2 table.

share|improve this question

1 Answer 1

Assuming sheet 1 has the dropdown for selecting input and sheet 2 has the full list of ISBNs and book titles.

  1. On sheet 1 select the cells you want the dropdown in. If you want a full column, click the column header for Sheet 1 column A.
  2. Then hit Alt + A + V + V which brings up the Data tab and then the Data Validation button and to the Data Validation... option.
  3. Allow: a List and select a Source: of =Sheet2!A:A. It must be a single-column source. Leave Ignore blank and In-cell dropdown checked.
  4. If you want the book title to show up in column B as each dropdown is selected: Click column B header, go to Data Validation... again and Allow: Custom and select a Source: of =IFERROR(VLOOKUP(A1,Sheet2!A:B, 2, FALSE),"").

You may want to remove duplicates on the ISBN/Title list on Sheet 2. Sorting it may also be handy.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.