1

I have 3 cells in one worksheet and I want these cells to be returned into one cell in another sheet. If the value in a cell is an empty circle Empty circle in first column then "Dip not aligned" will be returned else "Dip aligned will be returned. Below is my logic for trying to return the 3 values in one cell, Excel gives me an error saying too many arguments.

=IF( Worksheet1!P8 = "○", "DP Not Aligned", "DP Aligned", IF( Worksheet1!Q8 
"○", "DITP Not Aligned", "DITP Aligned" ), IF( Worksheet1!R8 = "○", "DIIP 
Not Aligned", "DIIP Aligned" ))
Royal
  • 21
  • 2
  • Perhaps a TEXTJOINIFS user defined function like the one found [here](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|83.6070#50719050). –  Jul 13 '18 at 00:17
  • 1
    What's the CODE or UNICODE for the `o` (or the filled bullet) ? –  Jul 13 '18 at 00:19

1 Answers1

1

You could use an ampersand & to concatenate the results from your IF() statements, don't forget to use brackets to separate the IF() statements first.

Try this:

Example values in spreadsheet:

P8|Q8|R8
○ |○ |

Formula:

="DP " &(IF( Worksheet1!P8 = "○", "Not ", "")& "Aligned|" &"DITP "&( IF( Worksheet1!Q8 ="○", "Not ", "" ))&"Aligned|"&"DIIP "&( IF( Worksheet1!R8 = "○", "Not ", "" )))&"Aligned"

Example output:

DP Not Aligned|DITP Not Aligned|DIIP Aligned

*(Edited for more condensed, clearer code suggested by @Jeeped in comments)

My original formula:

=(IF( P8 = "○", "DP Not Aligned", "DP Aligned")& "|" &( IF( Q8 ="○", "DITP Not Aligned", "DITP Aligned" ))&"|"&( IF( R8 = "○", "DIIP Not Aligned", "DIIP Aligned" )))
girlvsdata
  • 1,596
  • 11
  • 21
  • Ooh, I agree it could be made a little more modular like that, I'll edit my answer - thanks! – girlvsdata Jul 13 '18 at 01:00
  • @Jeeped Edited with both versions – girlvsdata Jul 13 '18 at 01:06
  • Hmm. Not really an appreciable difference but still a very valid alternative. –  Jul 13 '18 at 01:07
  • @Jeeped not much difference in formula length, but a big difference in readability and reusability. If you're familiar with Python programming, I'd say your code is the more 'Pythonic' of the two. – girlvsdata Jul 13 '18 at 01:10