SOLVED

Drop down list changes number to store number as text when select

%3CLINGO-SUB%20id%3D%22lingo-sub-1677601%22%20slang%3D%22en-US%22%3EDrop%20down%20list%20changes%20number%20to%20store%20number%20as%20text%20when%20select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1677601%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Using%20a%20Drop%20Down%20List%20to%20select%20a%20number%20formatted%20as%20a%20number%20from%20another%20table.%20However%2C%20when%20selected%20it%20converts%20the%20number%20to%20text.%20I%20have%20conditionally%20formatted%20the%20cells%20using%20the%20drop%20down%20list%20to%20colour%20code%20the%20result.%20When%20the%20number%20is%20converted%20to%20text%2C%20the%20colour%20conditional%20formatting%20does%20not%20work.%20I%20have%20to%20manually%20convert%20the%20numbers%20stored%20as%20text%20back%20to%20numbers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20ensure%20that%20the%20numbers%20remain%20formatted%20as%20numbers%20when%20selected%20form%20the%20drop%20down%20list%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1677601%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678282%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20down%20list%20changes%20number%20to%20store%20number%20as%20text%20when%20select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678282%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F797069%22%20target%3D%22_blank%22%3E%40HConlin_2012%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20data%20validation%20list%2C%20are%20there%20only%20numbers%3F%20If%20so%2C%20you%20could%20format%20the%20cell%20that%20contains%20the%20drop-down%20as%20Number.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678295%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20down%20list%20changes%20number%20to%20store%20number%20as%20text%20when%20select%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3BCells%20with%20drop%20down%20list%20are%20formatted%20for%20numbers%20but%20as%20soon%20as%20select%20from%20the%20list%20the%20value%20(that%20is%20a%20number%20from%20a%20cell%20also%20formatted%20as%20a%20number)%20changes%20to%20stored%20as%20text.%20Yes%20data%20validation%20list%20is%20all%20numbers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

      Using a Drop Down List to select a number formatted as a number from another table. However, when selected it converts the number to text. I have conditionally formatted the cells using the drop down list to colour code the result. When the number is converted to text, the colour conditional formatting does not work. I have to manually convert the numbers stored as text back to numbers.

 

Is there a way to ensure that the numbers remain formatted as numbers when selected form the drop down list?

 

Thanks in advance

7 Replies
Highlighted

Hello @HConlin_2012,

 

Make sure that the cell that contains the drop-down is formatted as Number or General.

Highlighted

@PReagan Cells with drop down list are formatted for numbers but as soon as select from the list the value (that is a number from a cell also formatted as a number) changes to stored as text. Yes data validation list is all numbers.

Highlighted

@HConlin_2012 

 

If possible, could you share a sample version of your workbook? (please remove any sensitive data of course)

Highlighted
Highlighted
Best Response confirmed by HConlin_2012 (New Contributor)
Solution

@HConlin_2012,

 

The reference for your data validation list was the header of a table. These header values are stored as text, hence why the numbers were stored as text. Please refer to changes in the attached workbook.

Highlighted

@PReagan - thanks, that makes sense. Didn't need to look at your mods - was able to correct so not referencing headers any more. Thanks again

Highlighted