Source currently evaluates to an error

[Magnetar]

Morning everyone.

Have got a small problem, regarding using the 'list' facility within Data | Validation... option.

I've set up a 'named' list on another worksheet, very small, with about three cells of data, and have given this a name, (e.g. 'StaffInitials').

I have then tried to set up a drop-down validation list, by entering =StaffInitials .

Have done this many times beforehand. However on this occasion I get the error/msge: 'Source currently evaluates to an error. Do you wish to continue?'. On clicking 'yes' the drop-down arrow is disabled.

Could anyone advise me on what could be producing this error/msge & how to avoid it please? Many thanX in advance.

Kind regards

Magnetar

 

[xlbo]

Are you sure your named range and what you have entered into Data>Validation are EXACTLY the same ?

If so, how have you defined your range ?? what is the address / formula used ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Magnetar]

Hi xlbo!

Many thanX for your quick response.

Just to give an example of what I did: I entered data across 3 cells, G3, G4 and G5 ('AB', 'BC', and 'CD', respectively), within WorkSheet1.

I selected all 3 cells, and within WorkSheet1, and within the Name Box (top left-hand of sheet), I typed 'StaffInitial' and pressed enter on my keyboard.

On Worksheet2, I selected the cell to which I require a drop-down list. Within Data | Validation... I entered '=StaffInitial' under 'List'.

However can't see why I should receive this msge, as I have used this facility before without any problems.

Any more ideas/solutions to this Geof/anyone else, please?
(Please let me know if any of the above I've mentioned is incorrect).
All help would be greatly appreciated. Thanks in advance.

Kind regards

Magnetar

 

[xlbo]

Please follow the menu path:
Insert>NAme>Define

highlight the StaffInitials name and post back with what you see in the "RefersTo" box

another test to perform is to enter

=INDEX(StaffInitials,1,1)

in any sheet and let me know what it returns

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Magnetar]

Hi Geoff,

ThanX for your response. Somehow I'd managed to sort this query out just after my second posting (but I'll take on board what you say for future reference!).

What I did was to select 'Insert | Name | Define...', then delete the 'StaffInitial' range.

I then set up the range name again, within the 'Name Box'.
This seems to have done the trick.

However many, many thanx for taking the time out to respond to my query. Much appreciated!!

Best wishes

Magnetar