Trying to target entire column in a different sheet with a formula

Hi, so what I'm working on is trying to automate status updates between different sheets. The first formula is looking at a status within the same sheet that the formula is written.

This formula is working correctly and has no issues. However when including a nested formula to look at a different sheet, that is where that problem lies. I need to be able to target the entire column in the other sheet, but when I do that the formula throws an error.


I have noticed however that if I target a single cell within that column, then the formula works correctly.

I'm at a complete loss as to what to do. I know I'm missing something but I'm not sure what that is, so any help would definitely be appreciated. Thanks

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Andrew Skaggs,

    For the cross sheet reference you can use COUNTIF combined with AND in your IF statement:

    =IF(AND([Assigned To]63 = "Yes", COUNTIF({Status}, "Approved") = 0), "Not Started", "Complete")

    If Assigned To row 63 is "No" and no rows in the status column are "Approved", you'll get a "Not Started" result, otherwise it will show as complete.

    You can easily change the AND to OR if only one condition needs to be true (you'll also need to change =0 to >0.

    Hope this helps somewhat, but if you've any problems/questions then just ask! 🙂

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    Hi @Andrew Skaggs,

    For the cross sheet reference you can use COUNTIF combined with AND in your IF statement:

    =IF(AND([Assigned To]63 = "Yes", COUNTIF({Status}, "Approved") = 0), "Not Started", "Complete")

    If Assigned To row 63 is "No" and no rows in the status column are "Approved", you'll get a "Not Started" result, otherwise it will show as complete.

    You can easily change the AND to OR if only one condition needs to be true (you'll also need to change =0 to >0.

    Hope this helps somewhat, but if you've any problems/questions then just ask! 🙂

  • Hi @Nick Korna,

    Thank you so much for all your help. That formula works perfectly and everything is now running smoothly. Again thank you I really appreciate it.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    No problem at all, happy to help! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!