Hunterisagrump, I take it you're saying that the actual task may involve more options than just two. This solution is a little involved, but it allows you an indefinite number of options. I'll explain this in R1C1 rather than A1 notation, and leave it to you to translate if you need to:
1) Put a string of foods (or whatever you're actually doing) in a single cell, with one space between each. My string says "str1 str2 sting3 food4 this5 longstring6 ". Make sure you include the space at the end of the string. Note that each word can be a different length. Let's say that value is in R2C1.
2) In another cell—say R1C1—put this formula: =FIND(" ",R2C1). It displays "5", because the first space is in position 5.
3) In subsequent cells on the same row, put =FIND(" ",R2C1,RC[-1]+1). This causes each cell in row 1 to display the position of each space in the string: 5, 10, 17, 23, 29 and 41.
4) Now allow your dropdown value to be anywhere from 1 to the number of words in R2C1 (up to 6 in this case). I didn't actually bother with a drop-down; I just put some number between 1 and 6 in R4C1.
5) In R5C1 put this formula: =LEFT(R2C1,INDIRECT("R1C"&R4C1,0)-1). This look at the number in R4C1 (or your dropdown cell), uses it to build an address of "R1Cn", and uses that address in a LEFT function to display the first part of the string in R2C1, to the proper length.
This doesn't allow for "and". Maybe you don't need "and"; maybe you can get by with a comma (which requires only minimal adjustment to the formulae). And I don't know whether "0" is an allowed response; you may have to figure that out too. But you should be able to adapt this method to give you more flexibility.
|