Populating 1 Cell from Multiple Cells within the Same Row

Karisa Ely

I'm trying to have a formula populate 1 cell, based on the responses in 6 columns in that same row.

If any of the 6 columns are blank OR have "No Scope", nothing should be displayed in the "Scopes" column for that particular scope. Otherwise, if there is text in any of those columns in that row, the "Scopes" column should display certain text. I currently have it as a dropdown selection and can get the formula working one off, but as soon as I start adding the others I get #unparsable or #incorrect argument.

I have tried IF/OR , JOIN/COLLECT

Current formula attempt:

=IF(OR(B@row = "", B@row = "No Scope"), "", "B Scope", IF(OR(C@row = "", C@row = "No Scope"), "", "C Scope", IF(OR(D@row = "", D@row = "No Scope"), "", "D Scope", IF(OR(E@row = "", E@row = "No Scope"), "", "E Scope", IF(OR(F@row = "", F@row = "No Scope"), "", "F Scope")))))

The first row in the below screenshot is what it should like return for that particular example. Nothing for A or B, but something for the others.

Really appreciate any guidance!

Paul Newcome

Instead of nesting your IF statements, try "adding" them together.

=IF(AND(A@row <> "", A@row <> "No Scope"), "A Scope" + CHAR(10)) + IF(AND(B@row <> "", B@row <> "No Scope"), "B Scope" + CHAR(10)) + IF(AND(C@row <> "", C@row <> "No Scope"), "C Scope" + CHAR(10))

Karisa Ely

Ahh, yesss!! Thank you! 🙌 I've been trying everything combination but never thought to try adding them.

This returns everything correctly, except if all are blank or have "No Scope" it returns 0. What do I add to get it to be blank ?

Paul Newcome

You would add this to the beginning:

=IF(COUNTIFS(A@row:C@row, AND(@cell <> "", @cell <> "No Scope") > 0, IF(AND(A@row <> "", A@row <> "No Scope"), "A Scope" + CHAR(10)) + IF(AND(B@row <> "", B@row <> "No Scope"), "B Scope" + CHAR(10)) + IF(AND(C@row <> "", C@row <> "No Scope"), "C Scope" + CHAR(10)))

Karisa Ely

That is returning incorrect argument.

Paul Newcome

That's because I missed a parenthesis. 🤦‍♂️

=IF(COUNTIFS(A@row:C@row, AND(@cell <> "", @cell <> "No Scope")) > 0, IF(AND(A@row <> "", A@row <> "No Scope"), "A Scope" + CHAR(10)) + IF(AND(B@row <> "", B@row <> "No Scope"), "B Scope" + CHAR(10)) + IF(AND(C@row <> "", C@row <> "No Scope"), "C Scope" + CHAR(10)))

Karisa Ely

HA! It's always a comma or parenthesis. Unfortunately, that one still kept returning 0. So after messing with it a bit I got this one to work:

=IF(OR(ISBLANK(A@row), A@row = "No Scope"), "", "A Scope" + CHAR(10)) + IF(OR(ISBLANK(B@row), B@row = "No Scope"), "", "B Scope" + CHAR(10)) + IF(OR(ISBLANK(C@row), C@row = "No Scope"), "", "C Scope" + CHAR(10))

Really appreciate your help and quick responses! Incredibly helpful! 🙌