Automating text based on information in other column

Erica Ringer

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.

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

Erica Ringer

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

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", "").

Erica Ringer

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

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

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

J. Craig Williams

Great answer Greg.

 

Craig