Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Update one sheet based on two columns from another sheet

Hello

I have a request sheet and a review sheet. The request sheet generates a task number after submission of a new row. That task number will be manually inputted into another sheet. Once a review is done of the request, the user hits review complete. I want that checkbox (once its clicked on the review sheet) to get checked off in the request sheet row based on the task number. Is that doable?

Best Answer

  • Community Champion
    Answer ✓

    @santiago.rendon

    Apologies for the confusion—I initially assumed you had a column that named the Task, rather than just the Task Number. Below is the corrected formula for the Review Complete column in the Request Sheet:

    =IFERROR(IFERROR(IF([Task Number]@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task number}, [Task Number]@row), 1)), "not in sheets")

    Breakdown of ranges:

    • {Review Sheet 1 Review Completed} → "Review Completed" column in Review Sheet 1
    • {Review Sheet 1 Task Number} → "Task Number" column in Review Sheet 1
    • {Review Sheet 2 Review Completed} → "Review Completed" column in Review Sheet 2
    • {Review Sheet 2 Task Number} → "Task Number" column in Review Sheet 2

    This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".

    Hope this helps.

    Peggy

Answers

  • Community Champion
    edited 01/24/25

    Hi @santiago.rendon

    I hope you're doing well!

    To set this up, follow these steps:

    1. Add a column to both sheets and name it "Review Complete". Make it a checkbox-type column.

    2. In the Request sheet, add the following formula in the Review Complete column:

    =IFERROR(INDEX({Review Sheet Review Completed}, MATCH([Task Number]@row, {Review Sheet - Task Number}, 0)), "Not in sheet")

    This will pull the "Review Completed" status from the Review Sheet based on the matching Task Number.

    Let me know if you have any questions.

    Thanks, Peggy

  • edited 02/05/25

    Hi Peggy,

    Thank you for your help, but I am running into an issue. I put in the same exact formula with basically the exact sheet you made just to test it out and I keep getting the 'not in sheet' line instead of a check box

    Am I missing another reference where {Review Sheet - Task Number} is?

    EDIT** I figured out it out, I had to remove the "{Review Sheet - Task Number}, 0))" part of the formula and it worked, I think it was the way you nicknamed the range.

  • Community Champion

    Hi @santiago.rendon

    I'm glad you figured it out! It's good practice to name a range instead of using the default one. I'd recommend updating Review Sheet Range 1 and Review Sheet Range 2 with a meaningful name.

    Have a great week!

    Peggy

  • Thanks @Peggy Parchert ,

    I did have one final question, can I add on to this formula to reference another sheet with the same "Review Complete" so 2 review sheets and 1 request sheet?

  • Community Champion

    Hi @santiago.rendon

    Yes, but you'll need to switch to INDEX/COLLECT for this to work. To demonstrate, I created Review Sheet 2, which is identical to Review Sheet 1.

    In the Request Sheet - Review Complete column, I added the following column formula:

    =IFERROR(IFERROR(IF(Task@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task}, Task@row, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task}, Task@row, {Review Sheet 2 Task Number}, [Task Number]@row), 1)), "not in sheets")

    Breakdown of the Ranges:

    • {Review Sheet 1 Review Completed}"Review Completed" column in Review Sheet 1
    • {Review Sheet 1 Task}"Task" column in Review Sheet 1
    • {Review Sheet 1 Task Number}"Task Number" column in Review Sheet 1
    • {Review Sheet 2 Review Completed}"Review Completed" column in Review Sheet 2
    • {Review Sheet 2 Task}"Task" column in Review Sheet 2
    • {Review Sheet 2 Task Number}"Task Number" column in Review Sheet 2

    This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".

    Let me know if you need any adjustments!

    Peggy

  • Thanks Peggy,

    I'm getting a little bit confused at the beginning of the formula with the (IF(Task@row) part since I don't have a column named "Task", can I replace that column with anything else? The only information that will always stay the same between sheets is the task number

  • Community Champion
    Answer ✓

    @santiago.rendon

    Apologies for the confusion—I initially assumed you had a column that named the Task, rather than just the Task Number. Below is the corrected formula for the Review Complete column in the Request Sheet:

    =IFERROR(IFERROR(IF([Task Number]@row <> "", INDEX(COLLECT({Review Sheet 1 Review Completed}, {Review Sheet 1 Task Number}, [Task Number]@row), 1)), INDEX(COLLECT({Review Sheet 2 Completed}, {Review Sheet 2 Task number}, [Task Number]@row), 1)), "not in sheets")

    Breakdown of ranges:

    • {Review Sheet 1 Review Completed} → "Review Completed" column in Review Sheet 1
    • {Review Sheet 1 Task Number} → "Task Number" column in Review Sheet 1
    • {Review Sheet 2 Review Completed} → "Review Completed" column in Review Sheet 2
    • {Review Sheet 2 Task Number} → "Task Number" column in Review Sheet 2

    This formula first checks Review Sheet 1 for a match. If no match is found, it looks in Review Sheet 2. If still no match is found, it returns "Not in sheets".

    Hope this helps.

    Peggy

  • @Peggy Parchert

    This worked! Thank you so much for all of your help, I really appreciate it!!

  • Community Champion

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions