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:
- someone may add something here at any time
However, now instead I see in the drop down list only:
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?