# 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

• ✭✭✭✭✭✭

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! 🙂

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭

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!