Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Automating text based on information in other column

Erica Ringer
edited 12/09/19 in Archived 2016 Posts

Is there a way to format something like this:

 

If column A is True than column B says blank? 

 

The reason I ask is that I have a report with site in one column and the next column should have id. It would be much easier for the id to popluate based on the site in the first column.

Tags:

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    What Kris is implying is that once someone enters data manually into column B, there is no going back to the formula - it is overwritten by the data entered manually.

     

    You might consider entering data in column C.

     

    =if([column A]23 = "True","",[column C]23)

     

    will show in column B (where the formula resides on row 23) blank if column A is "True" and will show the value of column C if not (if anything else)

     

    Craig

  • Thanks Craig and Kris. That makes sense. Could you help me understand that with an example, using fake info below?

     

    Column A = Campaign (Theme)

    Column B = ID

     

    If Campaign (Theme) is true, than ID = 123

     

    Also, where do you put the formula? Right in the column?

  • Greg Gates
    Greg Gates ✭✭✭✭✭
    edited 06/30/16

    Hi Erica!

     

    For the example you gave, you would put the formula in every cell in Column B. So the very first cell of column B should look like this:

     

    =IF([Campaign (Theme)]1 = "True", "123", "")

     

    This will set Column B to a value of "123" if the Campaign column is true, or blank if the Campiagn column is anything else. When you copy and paste this formula into other cells, the number should update accordingly. The second cell will automatically change to =IF([Campaign (Theme)]2 = "True", "123", "").

  • It worked!! Thanks. One last question...well two Can I do an or??

     

    Case 1:

     

    Column A = Site

    Column B = ID

     

    If Site is Google or Yahoo, than ID = 123

     

    Case 2:

     

    Column A = Site

    Column B = ID

     

    If Site is Google, than ID = 123 and if site is Yahoo, than ID = 456

  • Greg Gates
    Greg Gates ✭✭✭✭✭
    edited 06/30/16

    Absolutely! Although your two questions have pretty different answers.

     

    For question 1: That's what the "OR" function is for (although the format of the formula isn't necessarily intuitive). This time, your column B would look like this:

    =IF(OR(Site1="Google", Site1="Yahoo"), "123", "")

     

    You can put as many logical tests as you want inside the OR function. There's also an AND function that works in a similar way, but it only returns true if every test returns true.

     

    For question 2: You'll have to use nested if-statemets to accomplish this. Your formula in Column B would look like:

     

    =IF(Site1="Google", "123", IF(Site1="Yahoo", "456", ""))

     

    Basically, you're telling the column to go through a few steps:

    1. Is the Site equal to Google? If it is, put "123". If it isn't, go to step 2.

    2. Is the Site equal to Yahoo? If it is, put "456", If it isn't, leave this blank.

     

    You could extend this chain of If-statements as long as you want! There's a more detailed tutorial here: https://www.smartsheet.com/blog/support-tip-build-nested-IF

     

    I hope that helps!

  • KrisWalsh
    KrisWalsh ✭✭✭✭✭

    What do you want the column B value to be if column A is False?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Great answer Greg.

     

    Craig

This discussion has been closed.