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?
Range problem (Excel 2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Range problem (Excel 2003 SP3)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Range problem (Excel 2003 SP3)
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.
Option 2: format the column with the 'numbers' as Text. Enter 001 in the first cell, then fill down.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Range problem (Excel 2003 SP3)
Thanks HansHansV wrote: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.
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
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Range problem (Excel 2003 SP3)
You can, but that's not the point here.steveh wrote:Does that mean that you can't have true numbers in a named range
Excel considers numeric values and text values to be different, even if they look the same:
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans