Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-03-2020, 03:11 PM
hunterisagrump hunterisagrump is offline IF statement in a CONCANTENATE function Windows 10 IF statement in a CONCANTENATE function Office 2013
Novice
IF statement in a CONCANTENATE function
 
Join Date: Mar 2020
Posts: 2
hunterisagrump is on a distinguished road
Default IF statement in a CONCANTENATE function

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
File Type: xlsx dropdownexample.xlsx (8.5 KB, 2 views)
Reply With Quote
  #2  
Old 03-03-2020, 04:03 PM
jeffreybrown jeffreybrown is offline IF statement in a CONCANTENATE function Windows 10 IF statement in a CONCANTENATE function Office 2016
Expert
 
Join Date: Apr 2016
Posts: 607
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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"),"")
Reply With Quote
  #3  
Old 03-08-2020, 03:35 AM
hunterisagrump hunterisagrump is offline IF statement in a CONCANTENATE function Windows 10 IF statement in a CONCANTENATE function Office 2013
Novice
IF statement in a CONCANTENATE function
 
Join Date: Mar 2020
Posts: 2
hunterisagrump is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 03-08-2020, 02:58 PM
BobBridges's Avatar
BobBridges BobBridges is offline IF statement in a CONCANTENATE function Windows 7 64bit IF statement in a CONCANTENATE function Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 644
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting a Select statement in Excel to an update statement shabbaranks Excel Programming 5 10-31-2018 11:47 PM
IF statement in a CONCANTENATE function WEEKNUM function not adhering to the IF statement darkoman Excel 1 06-09-2016 06:28 AM
IF statement in a CONCANTENATE function Restrict Editing function disable insert textbox function IanM_01 Word 5 11-21-2015 02:29 AM
IF statement in a CONCANTENATE function concantenate cells in multiple row shilabrow Excel 7 03-26-2015 06:42 PM
IF statement in a CONCANTENATE function Mail Merge Function not Noticing 'True' vs. 'False' Statement danellekent Mail Merge 1 04-27-2012 02:19 AM


All times are GMT -7. The time now is 10:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft