microsoft-excel-training - drop down lists

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » microsoft-excel-training - Drop down lists

microsoft-excel-training - Drop down lists

resolvedResolved · Low Priority · Version Standard

Drop down lists

I want to create a drop down list in Cell A1 containing say these references DR1 DR2 DR3 DR4 etc, and in cell B1 I wish to show a prepared description of the item when selected,
so when I select DR1 it shows a description and when DR2 is selected it changes description etc.
Maybe Cell C1 will show a price for the particular item that is selected .
I look forward to your easiest solution.
many thanks

RE: 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

Attached files...

vlookup & data validation.xls

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips


Server loaded in 0.1 secs.