Number format in Excel drop down lists?

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 5, 2010

• #2

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.

 

Upvote 0

I

iswarya

New Member

Joined

Apr 20, 2010

Messages

18

• May 5, 2010

• #3

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 5, 2010

• #4

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?

 

Upvote 0

I

iswarya

New Member

Joined

Apr 20, 2010

Messages

18

• May 5, 2010

• #5

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.

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 5, 2010

• #6

Can you give an example formula please?

 

Upvote 0

NBVC

Well-known Member

Joined

Aug 31, 2005

Messages

5,828

• May 5, 2010

• #7

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...

 

Upvote 0

I

iswarya

New Member

Joined

Apr 20, 2010

Messages

18

• May 5, 2010

• #8

[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.

 

Upvote 0

NBVC

Well-known Member

Joined

Aug 31, 2005

Messages

5,828

• May 5, 2010

• #9

Care to share?

May help future users with similar problems.

 

Upvote 0

I

iswarya

New Member

Joined

Apr 20, 2010

Messages

18

• May 5, 2010

• #10

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.

 

Upvote 0