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

Andrew Skaggs

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.

1.PNG

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.

3.PNG

2.PNG

4.PNG

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

5.PNG

7.PNG

6.PNG

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

Nick Korna

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

Nick Korna

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

Andrew Skaggs

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

No problem at all, happy to help! 😊