microsoft-excel-training - Drop down lists

Hello Stephen,
Hope you enjoyed your Microsoft and Macromedia training with us.

The question you are asking about would most easily be answered using a combination of VLOOKUP and DATA VALIDATION.

To start you will want to have a sheet that contains the references in Column A, the description in Column B and the price in Column C. You can enter as many columns as you want details for. Lets name this sheet DATA for easy reference.

Next you will want to create the main sheet that you will be using to lookup the values from the sheet that contains all the details. By using the VLOOKUP function, you can lookup a reference that you specify, and return a related cell. Lets call this sheet LOOKUP.

So in Row 1 in the LOOKUP Sheet you want to type some headings to label the information you will be finding.
In A1, lets type SEARCH. In B1 type DESCRIPTION, and in C1 type PRICE.

In A2, lets type a reference you want to lookup, say DR1. We will create a drop down list later. Now in B2, create a VLOOKUP function like the following:

=VLOOKUP(A2,DATA!A1:C7,2,FALSE)

In Cell C2, enter the following formula

=VLOOKUP(A2,DATA!A1:C7,3,FALSE)

This formula takes the value you enter into A2 and then finds it in the first column in the list on the DATA sheet. Then it returns the column you specify (2) or (3) accordingly.

This is the core of the system.
-----
DROP DOWN LIST

To create the drop down, we need to name the range from which the list comes from. Go to the DATA sheet, and highlight the first column, excuding any headers. Now click in NAME BOX, which you can find to the left of the FORMULA BAR. When you click into the box (usually it contains a cell reference of sone sort), enter a name for the selected range (lets go for LIST).
PRESS ENTER (you must press enter here or it will not work.

No go back to the LOOKUP sheet.
Click on A2 and then from the menu bar, click DATA, then VALIDATION.
From the ALLOW option, choose List, and then in the SOURCE box type:

=list

Click enter.

---

At this point you will have the system you have requested.
I have attached a spreadsheet that details the system as described above.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Richard
Microsoft Office Specialist Trainer