# Populating 1 Cell from Multiple Cells within the Same Row

✭✭✭

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!

• ✭✭✭✭✭✭

=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))

• ✭✭✭

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 ?

• ✭✭✭✭✭✭

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)))

• ✭✭✭

That is returning incorrect argument.

• ✭✭✭✭✭✭

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)))

• ✭✭✭

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))