Number format in Excel drop down lists?

[iswarya]

I have 2 list of numbers in a drop down list in a cell in Excel. The problem is Excel does not allow me to "type in" these values from the keyboard for the list that has numbers (but I'm able to type in the list that has "names"). I'm guessing this is because lists are "text" by nature in Excel? I want to be able to type in as well as choose in both the lists. Is there a solution/workaround to this?

Cross Posted:
http://www.excelforum.com/excel-gen...mat-in-excel-drop-down-lists.html#post2300717

 

[Andrew Poulsom]

Where is your drop down list from (Control Toolbox, Formas Toolbar or Data Validation)? You are right that the list is text, but I had no problem typing numbers in a ComboBox from the Control Toolbox.

 

[iswarya]

I see. Well, mine is from Data Validation only.

 

[Andrew Poulsom]

I had no problem typing a number in a data validation cell that was bound to a list, provided that the number was in the list. Can you give a specific example?

 

[iswarya]

Okay so I have this cell which has data validation bound to a list of numbers (say $B$1:$B$100). I am able to choose from the list of numbers I have, but when I try to key in a number (which is present in the list), it still gives me a validaton error alert.

Btw, the list of numbers in B1:B100 are themselves output of formulas from another column - I don't know if this is relevant, but I thought I'll state it anyways.

 

[Andrew Poulsom]

Can you give an example formula please?

 

[NBVC]

If the number range is formatted as text, then you will have this problem if you type in a number.

If you can type a number that is within the list preceded with an apostrophe and it works, then that is the problem

If you have formulas in those cells in the list, make sure that if you have them returning numbers, that they are not enclosed in double quotes.

e.g. =IF(A1="X",1,2) not =IF(A1="X","1","2")

otherwise, select the list and go to Data|Text to Columns and simply click Finish... then reselect the range and Format as General or Number...

 

[iswarya]

[SOLVED] Number format in Excel drop down lists?

Thank you guys for your valuable help! We found a way to work around the issue, and not type numbers at all.

 

[NBVC]

Care to share?

May help future users with similar problems.

 

[iswarya]

It's a kind of a work around to the project, rather than a solution to the issue itself. As there were two columns, one for number and another for name, we decided to allow the users to type names, but only choose the numbers. Turns out more often they need to type names than numbers. Yeah, I know, it's kind of like running away from the problem, but our end user okayed this.