excel: the name but not the contents of a named range is the only option shown in drop down list

Question

I have a sheet 'orders' and a sheet 'lists.' In lists, I have a list of valid fruits:

a1:  fruits
a2:  apple
a3:  orange
a4:  someone may add something here at any time

I have a named range 'fruits' in the workbook (not in the sheet) with the value "fruits!$a$2:index(fruits!$a$2:$a$100, counta(fruits!$a$2:$a$100))." With this setup, I press CtrlG to open the 'go to' box and then type in 'fruits' and hit return, then cells a2, a3, and a4 are correctly selected in tab 'lists.'

In sheet 'orders' I have header in column b1 'the fruit,' and I have selected all the cells below the header in b1 and have gone to Data -> Data Validation, Allow -> List, Source -> fruits and pressed ok. Then I click on a cell below b1.

Usually, and elsewhere for similar situations in this workbook, this works and in the resulting drop down list I correctly see:

• apple
• orange
• someone may add something here at any time

However, now instead I see in the drop down list only:

• fruits

What gives? I've successfully maneuvered this process in hundreds of documents in the past. Now it isn't working - has this happened to you, too? Why does this error manifest?

NOTE: I have manual calculation ticked. Does this matter?

Answer 1

This must be some kind of bug. Given the situation above, click on orders!a2 and go to Data -> Data Validation, Allow -> List, Source -> delete fruits and replace with lists!$a$2:$a$4 and press OK. Click on orders!a2 and a drop down box will appear as expected with:

• apple
• orange
• someone may add something here at any time

Now go into the same dialog box, overwrite with 'fruits' again in the source, and press OK. Now everything works as expected. Anyone know what went wrong?