hello all, I sure hope someone has a bigger brain than I do, because I've been slamming my head against my desk all day with this.

in A1, I have a drop down with "2,3" in it. the cell is named DROPDOWN
in A2, I have a drop down with "pizza,sushi" in it. this cell is named FOOD1
in A3, I have a drop down with "pizza,sushi" in it. this cell is named FOOD3

I think have a CONCANTENATE function in B5 that starts "My favorite foods include tacos"

then, i'm trying to include 2 IF statements.
IF(DROPDOWN>=2, "and ",FOOD1," ","")
IF(DROPDOWN>=3, "and ",FOOD2," ","")

the ideas is that if the number 2 is selected in DROPDOWN, it reads "My favorite foods include tacos and FOOD1", and if the number 3 is selected in DROPDOWN, the output of B5 reads "My favorite foods include tacos and FOOD1 and FOOD2"




this is what is in B5, and what is not working

=CONCATENATE("My favorite foods include tacos",=IF(DROPDOWN>=2, "and ",FOOD1," ","") ",=IF(DROPDOWN>=3, "and ",FOOD2," ","")")

this is a silly example of something I'm trying to do for a work document, where macros are not allowed. Any help would be greatly appreciated!

Attached Files

dropdownexample.xlsx (8.5 KB, 7 views)

I'm not entirely sure if you are wanting the actually food names from the dropdowns in A2 and A3, but how about...

=IFERROR(CHOOSE(MATCH(DROPDOWN,{2,3},0),"My favorite foods include tacos and FOOD1","My favorite foods include tacos and FOOD1 and FOOD2"),"")

I think that would probably work fine for smaller entries like the crummy example I gave. I'm trying to do a fairly large modification with line breaks and such. I'll try it out and get back to you!

thank you

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.