Range problem (Excel 2003 SP3)

[steveh]

Hi all

I have a named range called ByPrefix which has 6 columns, the range begins at 001 and goes through to 999 they must be displayed in the Combo as 3 numbers to make any sense

1. If I enter them as normal numbers 001 - 999 it shows numbers less than 100 in the Combo as 1 or 23 or 57 and if I use a formula ike this =VOOKUP(D17,ByPrefix,2,False) nothing in the expected cell displays
2. If a change all of the numbers <100 to '001 or '010 and select anything less than 100 all 6 columns are populated using the formula (with changes for the column =VOOKUP(D17,ByPrefix,2,False) etc.) but nothing greater than 100 selected in the Combo populates the fields.
3. If I make the numbers a custom format by using 000 they display in the range correctly but in the combo those less than 100 show as 1 or 17 or 59 etc. and nothing for any number 0001 - 999 fills the other boxes
4. If I do a search and replace and change the 0 (zero) to O everything is populated OK but it looks very scruffy
5. If I change it to General or Text it makes no difference

I am a bit flummoxed and don't know what to try next?

[HansV]

Option 1: prefix ALL values, not only those < 100, with an apostrophe '

Option 2: format the column with the 'numbers' as Text. Enter 001 in the first cell, then fill down.

[steveh]

Option 1: prefix ALL values, not only those < 100, with an apostrophe '

Option 2: format the column with the 'numbers' as Text. Enter 001 in the first cell, then fill down.

Thanks Hans

As it was not a true list of numbers as some were missing, for example 001, 002, 005 etc. I could not use the drag down option so I used an intermediary cell to place an apostrophe and then merged them into another cell and it worked well.

Does that mean that you can't have true numbers in a named range because I have just tried to do the same with an ISO list of Countries where you can search by Country, by 2 letter code, by 3 letter code or by Country number and whilst the actual numbers showed up in the Combo, 32 for Argentina for example, it would not show the other 3 columns with a VLOOKUP until I made the number '032

[HansV]

Does that mean that you can't have true numbers in a named range

You can, but that's not the point here.
Excel considers numeric values and text values to be different, even if they look the same:

S307.png

As a consequence, functions such as MATCH and VLOOKUP return #N/A if the search value is text and the lookup column contains numbers, or vice versa.
The value of a combo box is text, so you must either convert this value to a number, or ensure that the lookup column is text.