If then formula help

TullyONeill

Hi Community! I have been trying to get a few if then formulas but, I'm having lots of issues.... Typing out the scenario and hoping that all of your brilliant people can help me figure this out...

Have a big SS. Certain projects will need a work breakdown schedule with a start to finish planner. All projects need certain information such as codes.

For the projects that have that WBS, I have a "project facts summary before the tasks which are broken down in rows. Here's a pic of what I've got

I want a formula that says if the word "veritas" is in the column "task type" , I want it to pull the data that's in that's in the column called "veritas" and put it in the column Description in the same row. Below you'll see the veritas code column and the code example

jmyzk_cloudsmart_jp

Hi @TullyONeill

To find if the cell value at the "Task Type" column in the 4th row is "Verital Code", use:

IF([Task Type]@row="Veritas Code"

or

IF([Task Type]4="Veritas Code"

But I wonder why you want to use IF THEN here.

The formula to get the "Verital Code" from the second sheet to the "Description" column at the Veritas Code row in the first sheet would be something like this;

=JOIN({Veritas Code column range of the second sheet})

assuming there is only one entry of the Verital Code in the column.

A similar operation happens when you want to get data from the intake sheet (Data sits horizontally) to a Summary column (data sits vertically).

In that case, we usually use VLOOKUP or INDEX(MATCH type of cross-sheet reference.

https://app.smartsheet.com/b/publish?EQBCT=2af7f8ebab8d4d89a6050741d9fab985

TullyONeill

Hi! thanks for the help with this! When I type in the formulas you provided, nothing is happening. It just pastes the text into the cell. Below you'll find the image. Do I need to change something to make this work?

jmyzk_cloudsmart_jp

Hi @TullyONeill

I mistook what you want.

So, you want if the word "veritas" is in the cell [Task Type]4, get the value in the "Veritas Code" column; the formula would be:

If the "Veritas Code" column is in the same sheet

(Your question shows two separate images of sheets. But you may have separated a sheet into two images.)

• =IF(CONTAINS("veritas", [Task Type]4), [Veritas Code]@row)

First, you need to put "=" before the IF statement.

CONTAINS("veritas",[Task Type]4) means that the cell [Task Type]4 contains "veritas". As shown in the image of the first sheet below, I used the CONTAINS function, not the FIND function, to determine if the word "veritas" is in the cell because FIND is case-sensitive.

Since you have the "Veritas Code" in the same sheet, you get the value with [Veritas Code]@row or [Veritas Code]4.

if the position "Veritas Code" in the Task Type is fixed at the 4th row

However, if the position "Veritas Code" in the Task Type is fixed at the 4th row, you do not have to use the IF statement. So, the formula is:

• =[Veritas Code]@row

If the "Veritas Code" column is in another sheet.

In this case, you need to use a cross-sheet reference formula like this;

• =IF(CONTAINS("veritas", [Task Type]@row), JOIN({Another Sheet Range Veritas Code}))

The JOIN({Another Sheet Range Veritas Code}) part joins any values in the {Another Sheet Range Veritas Code} range, which is shown on the lower right-hand corner image.

https://app.smartsheet.com/b/publish?EQBCT=18e2c4b8d1eb4f1ba1fbed7bc36bdaa3