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
-
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
-
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.
-
No problem at all, happy to help! 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!