Pull multiple IF THEN statements from data sheet?

CB162023
CB162023 ✭✭
edited 08/25/23 in Formulas and Functions

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


Tags:

Best Answer

  • Shanky Paul
    Shanky Paul ✭✭✭✭
    Answer ✓

    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,

Answers

  • Shanky Paul
    Shanky Paul ✭✭✭✭
    Answer ✓

    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
    CB162023 ✭✭
    edited 08/28/23

    @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
    Shanky Paul ✭✭✭✭

    @CB162023 Glad it helped! Have a nice day!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!