dropdown menu shows the row number instead of value

**redbullah** · 05-03-2010, 03:55 PM

hello. i have a dropdown menu with these values in it:

5
23
56
235

when i pick 23 in the dropdown menu, excel shows 2 in the linked cell. same for others, it shows 4 when i pick 235.

how can i make excel show the value, not the row number?

**contaminated** · 05-03-2010, 04:00 PM

Sounds like u use Combobox (Form Control). This is proper behaviour of this control. It returns an index of selected item...

U can use this function say in D1
=index(A1:A4,B1)
Where B1 is your linked cell and A1:A4 is the range where numbers are stroed

**Palmetto** · 05-03-2010, 04:06 PM

Why not just use a Data Validation drop down using the list option then you can directly reference the drop down cell value? It is simpler to and doesn't require any extra formulas or cells.

**redbullah** · 05-03-2010, 04:28 PM

Originally Posted by Palmetto

Why not just use a Data Validation drop down using the list option then you can directly reference the drop down cell value? It is simpler to and doesn't require any extra formulas or cells.

can you please tell me how to do that?

**fredpox** · 05-03-2010, 05:05 PM

Hi again redbullah,
in the attached document I show you how data validation works.

In this link you can find more on Excel data validation.

regards,
P.

**redbullah** · 05-03-2010, 05:26 PM

Originally Posted by fredpox

Hi again redbullah,
in the attached document I show you how data validation works.

In this link you can find more on Excel data validation.

regards,
P.

guys, thanks for your responses. i understand how to use data validation, but have a problem. i can't choose the list from another worksheet, actually i can't switch between worksheets while the data validation dialog box is active.

**Palmetto** · 05-03-2010, 05:29 PM

See this Introduction to Data Validation
In Excel 2007, with cell selection which will hold the Data Validation, click on the Data tab in the ribbon menu, then click on Data Validation and choose the List option under the "Allow" drop down.

In the "Source" box, enter the list of values you want displayed in the list with each value separated by a comma. Click OK. Now the cell has a data validation list that uses the numbers you entered.

Explore the link to gain more understanding. The above is very basic.