Pull multiple IF THEN statements from data sheet?

CB162023

I have a data sheet with 4 Columns that I'd like to pull into a second. In this simplified example, the Data sheet shows phase, activities which repeat for each phase and a date. I also have a column with "Yes/No" if I need the date in the Smartsheet.

What formula do I use to get the data to my second layout? I'd like it to run "If row equals "Deliver Material" and "Phase 1" and "Y" --> then put in the date (highlighted yellow). TIA

Shanky Paul

Hi,

Try the below formula:

=IFERROR(INDEX(COLLECT({Select "Date" range column from data sheet}, {Select "Phase" range column from data sheet}, Phase@row, {Select "Activity" range column from data sheet}, "Deliver Material", {Select "run" range column from data sheet}, "Y"), 1), "")

For the other columns, copy the same formula and change the activity name (like below).

=IFERROR(INDEX(COLLECT({Select "Date" range column from data sheet}, {Select "Phase" range column from data sheet}, Phase@row, {Select "Activity" range column from data sheet}, "Install Material", {Select "run" range column from data sheet}, "Y"), 1), "")

Thank you,

Shanky Paul

Hi,

Try the below formula:

=IFERROR(INDEX(COLLECT({Select "Date" range column from data sheet}, {Select "Phase" range column from data sheet}, Phase@row, {Select "Activity" range column from data sheet}, "Deliver Material", {Select "run" range column from data sheet}, "Y"), 1), "")

For the other columns, copy the same formula and change the activity name (like below).

=IFERROR(INDEX(COLLECT({Select "Date" range column from data sheet}, {Select "Phase" range column from data sheet}, Phase@row, {Select "Activity" range column from data sheet}, "Install Material", {Select "run" range column from data sheet}, "Y"), 1), "")

Thank you,

CB162023

@Shanky Paul - THANK YOU! My kids suck all my brain energy out and I could not have done this without your response.

I actually have 16 columns and multiple rows in my sheet, so I did a little tweaking so I could copy/paste the formula throughout the document.

1. Added a "ghost" row on the spreadsheet in Row 1 that repeats the Activity Name. (i.e. "Deliver Material" "Install Material")
2. Added Absolute cell references on my row and column.

=IFERROR(INDEX(COLLECT({SMARTSHEET DATA Range 3}, {SMARTSHEET DATA Range 1}, $Phase@row, {SMARTSHEET DATA Range 2}, [Install Materials]$1), 1), "N/A")

Range 3 = Date; Range 1 = Phase; Range 2 = Activity

*I didn’t end up needing the “Run” criteria since it was matching based on the phase and activity*

Again, thank you thank you thank you!

Shanky Paul

@CB162023 Glad it helped! Have a nice day!