Sep 17 2020 03:44 AM
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
Sep 17 2020 06:26 AM - edited Sep 17 2020 06:28 AM
Hello @HConlin_2012,
Make sure that the cell that contains the drop-down is formatted as Number or General.
Sep 17 2020 06:31 AM
@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.
Sep 17 2020 06:46 AM
If possible, could you share a sample version of your workbook? (please remove any sensitive data of course)
Sep 17 2020 06:57 AM
@PReagan Sure
Sep 17 2020 07:29 AM
Solution
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.
Sep 17 2020 07:52 AM
@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
Sep 17 2020 08:38 AM
Sep 17 2020 07:29 AM
Solution
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.