How best to copy partial information from one column into another?

I am using IF(ISBlank) and IF(Contains) together to automatically populate a a cell in one column from data in an adjacent cell/column, but the formula won't let me add any more combinations of the formula to complete this task for about 80 pieces of data. (The formula tops out at 61 in this scenario and won't let me add any more keystrokes.) I'm sure there's a cleaner, quicker solution, but I'm not a Smartsheet guru.

The scenario is:

Column A contains a contact list of ~600 names associated with their company (~80 companies) in this format: Last Name, First Name - Company Name. It is in a multi-select drop-down column. When a user selects a contact (or multiples), I want the Company Name to automatically populate into the adjacent cell in Column B.

This formula works:

=IF((ISBLANK([COLUMN A]@row)), "None", (IF(CONTAINS("Company Name",[COLUMN A]@row, "Company Name", "") +IF(CONTAINS(

... but, like I noted, Smartsheet can only handle about 60 of the 80 names referenced in the repeating formula.

Stumped. What's the better way?

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

    It sounds like you may be hitting cell character limits. What about splitting the formula into the First 40 and then adding another column to return the Second 40, and in a third column add together the two outputs?

    Cheers,

    Genevieve

  • David Brandt
    David Brandt ✭✭✭✭
    Answer ✓

    Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

    Ex:

    =[Column 1]@row + " " + [Column 2]@row

    In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

    I would actually say since your final column will be adding the two cells together, if none of the company names are shows then we can put that statement in your final formula instead of into the two halves.

    Ex:

    =IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row


    So then your two halves would go right into your IF(CONTAINS statements:

    =IF(CONTAINS("Company One", [COLUMN A]@row), "Company One" + CHAR(10)) + IF(CONTAINS("Company Two", [COLUMN A]@row), "Company Two" + CHAR(10)) + IF(CONTAINS("Company Three", [COLUMN A]@row), "Company Three" + CHAR(10)) + IF(CONTAINS("Company Four", [COLUMN A]@row), "Company Four" + CHAR(10)) + IF(CONTAINS("Company Five", [COLUMN A]@row), "Company Five" + CHAR(10))

    ... etc


    Let me know if that makes sense and will work for you!

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    My apologies for the delay!

    The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

    Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

    Try:

    =IF(ISBLANK(ANALYSTS@row), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10), "") + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10), "") + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10), "") + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10), "")))

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

    It sounds like you may be hitting cell character limits. What about splitting the formula into the First 40 and then adding another column to return the Second 40, and in a third column add together the two outputs?

    Cheers,

    Genevieve

  • David Brandt
    David Brandt ✭✭✭✭
    Answer ✓

    Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

  • David Brandt
    David Brandt ✭✭✭✭

    Hi @Genevieve P. - Haven't seen any other options offered so I'm going to handle the formula in the manner you suggest... splitting the formula into 2 columns. Do you know how you would then combine the outputs into a singular column?

    Also, in the cells where the result is multiple company names, how would I either include a semicolon or some other divider between each company name? (I think I can just include that after each company name in the formula, but then I also want to eliminate that divider when only one company name populates into the cell. Any ideas on that one?

    All the best,

    David

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

    Ex:

    =[Column 1]@row + " " + [Column 2]@row

    In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

    I would actually say since your final column will be adding the two cells together, if none of the company names are shows then we can put that statement in your final formula instead of into the two halves.

    Ex:

    =IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row


    So then your two halves would go right into your IF(CONTAINS statements:

    =IF(CONTAINS("Company One", [COLUMN A]@row), "Company One" + CHAR(10)) + IF(CONTAINS("Company Two", [COLUMN A]@row), "Company Two" + CHAR(10)) + IF(CONTAINS("Company Three", [COLUMN A]@row), "Company Three" + CHAR(10)) + IF(CONTAINS("Company Four", [COLUMN A]@row), "Company Four" + CHAR(10)) + IF(CONTAINS("Company Five", [COLUMN A]@row), "Company Five" + CHAR(10))

    ... etc


    Let me know if that makes sense and will work for you!

    Cheers,

    Genevieve

  • David Brandt
    David Brandt ✭✭✭✭

    Hi there @Genevieve P. Thumbs up! Thanks for coming back on this. CHAR (10) is a great solution to the divider... much cleaner than how I was approaching! I'll work through your formulas tomorrow, but can see how this will solve my conundrum. Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    I'm glad I could help! 🙂

  • David Brandt
    David Brandt ✭✭✭✭

    Hi @Genevieve P. Just wanted to circle back with an interesting hiccup. The formulas are doing what they are suppose to but there is some weirdness in the results that I can't figure out. Maybe you've come across this: When there isn't a company name listed in both Formula columns the numeral zero appears in the column without a company listed. And then that zero is populated into the combined column.

    There are also instances where a zero will appear in front of the company name "0company e" for instance instead of "company e" in one of the Formula columns (and of course that gets pulled over to the combined column as well). This seems to have something to do with certain combinations of company names, or where they sit within the formula. It is not random, but I can't establish an exact pattern.

    FIRM1 Column...

    =IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10)) + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10)) + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10)) + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10))))

    FIRM2 Column...

    =IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company e", ANALYSTS@row), "company e" + CHAR(10)) + IF(CONTAINS("company f", ANALYSTS@row), "company f" + CHAR(10)) + IF(CONTAINS("company g", ANALYSTS@row), "company g" + CHAR(10)) + IF(CONTAINS("company h", ANALYSTS@row), "company h" + CHAR(10))))

    Weird?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @David Brandt

    My apologies for the delay!

    The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

    Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

    Try:

    =IF(ISBLANK(ANALYSTS@row), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10), "") + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10), "") + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10), "") + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10), "")))

  • David Brandt
    David Brandt ✭✭✭✭

    Hi there @Genevieve P.

    Thanks for the reply. Yes, Smartsheet's little quirks and needs take some getting use to. I came across one of your other exchanges and found / tried that fix , "". And it worked just as you indicated in that comment as well. Thank you very much.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!