Drop down selection no longer works

[ipbr21054]

Hi,
This morning i came to use the drop down list but nothing happened.

If i select the cell then data validation i see the following,
SETTINGS
Allow LIST which is correct
Source =HONDANUMBERS which is correct

I click on OK but see an error message The source currently evaluates to an error

Any advice on how to track this down.
Thanks

 

[Joe4]

Is HONDANUMBERS a named range?
If you go to the Name Range manager, do you see it listed there, and does it show a valid range being assigned to it?

 

[ipbr21054]

Hi,
I have 4 of the same name BUT 3 of them for some reason show#REF #REF ??

 

[Joe4]

I have 4 of the same name BUT 3 of them for some reason show#REF #REF ??

You cannot re-use the same name, it must be unique.
Delete the three bad ones, then see if your drop-down works.

 

[ipbr21054]

The names are the same BUT for a different worksheet which is in the same workbook.

So HONDANUMBER .....INFO
HONDANUMBERS....SALES
HONDA NUMBER.....INV

I await reply before i do anything.

 

[ipbr21054]

Maybe look at this with one eye closed.

 

[Joe4]

HONDANUMBER
HONDANUMBERS
HONDA NUMBER
all appear to be slightly different names, which would be all right.
Which one if your Data Validation referencing?

Regardless, if is referencing one with #REF errors, that won't work, as you do not have a valid define range for that Named Range.
You need to fix that.

 

[ipbr21054]

Take a look at post #6 as i think you missed it when you posted #7

 

[Joe4]

I don't know how you were able to have multiple Named Ranges with the same name. I tried doing that myself, and Excel flat out won't let me do that. I even tried by VBA, and when I did that, it overwrote it instead of adding it.

In any event, I would highly recommend using different names, so you do not have multiple named ranges with the same name. Your Data Validation might be confused and not know which one to use.

 

[ipbr21054]

The name is the same but the worksheet it is for are different.
Looking at the name manager this happens quite a lot.

So to clean it up should i use the named range BUT select workbook then each worksheet can use it.
Is that how it should be done ?