Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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?

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.