If And Formula not working
My formula is not working but I also don't know that I am using the correct formula. I am trying to pull a number from a different sheet (a number from the PAC Weekly freezer reading max temp column) and want to pull that number only if it also has a match of the date from Week 1 and the freezer number
=IF(AND({PAC Weekly Freezer Readings Date} = [Week 1]@row, {PAC Weekly Freezer Number} = [Freezer/Fridge #]@row), {PAC Weekly Freezer Readings Max Temp}, "")
Best Answer
-
The issue with your formula is that your positive condition in you IF just gives the system a range of values, which it doesn't know what to do with in an IF. INDEX/COLLECT should work much better here:
The syntax is: =INDEX(COLLECT({Rate Value}, {Criterion 1 Range}, [Criteria 1]@row, {Criterion 2 Range}, [Criteria 2]@row), 1)
So your formula would be:
=INDEX(COLLECT({PAC Weekly Freezer Readings Max Temp}, {PAC Weekly Freezer Readings Date}, [Week 1]@row, {PAC Weekly Freezer Number}, [Freezer/Fridge #]@row), 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
The issue with your formula is that your positive condition in you IF just gives the system a range of values, which it doesn't know what to do with in an IF. INDEX/COLLECT should work much better here:
The syntax is: =INDEX(COLLECT({Rate Value}, {Criterion 1 Range}, [Criteria 1]@row, {Criterion 2 Range}, [Criteria 2]@row), 1)
So your formula would be:
=INDEX(COLLECT({PAC Weekly Freezer Readings Max Temp}, {PAC Weekly Freezer Readings Date}, [Week 1]@row, {PAC Weekly Freezer Number}, [Freezer/Fridge #]@row), 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you so much! That was so helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!