INDEX/COLLECT FORMULA PUSHING OUT "#INVALID VALUE" - CAUSING CHECKBOX FORMULA TO FILL IN WITH SAME
I'm trying to gauge training impacts for different business areas within my organization for specific projects. I have a sheet in the background that's pulling if the business area has marked if they have a "training impact" or not in relation to a specific project.
Example:
Business Area 1 responded "Yes" to having impacts for Project 1.
My formula underneath the "Impact Response" field is the following:
=INDEX(COLLECT({Project Training Request Form - Impacts}, {Project Training Request Form - Business Area}, [Impacted Areas]@row, {Project Training Request Form - Project Name}, [Project Name]@row), 1)
This formula works if we have gotten a response from a business area (whether it's a yes or a no), however, if we have no response at all it will push out "#INVALID VALUE".
In order to "pretty up" my sheet to share out I put in a formula underneath "Impacts" and the "No Impacts" field to auto check the "Impacts" box if we got the response of "Yes". I have done the same thing with the "No Impacts" column.
The issue I am having is it's pushing out "INVALID VALUE" as well if there's no response. This would be fine if I weren't trying to share this out with stakeholders on a Dashboard, but I am. I have tried locking the column to just be a checkbox (then forgot I still wanted to formula to run so I had to fix it).
Any help? Suggestions?
Best Answers
-
Try an IFERROR
=IFERROR(INDEX(COLLECT({Project Training Request Form - Impacts}, {Project Training Request Form - Business Area}, [Impacted Areas]@row, {Project Training Request Form - Project Name}, [Project Name]@row), 1), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try an IFERROR
=IFERROR(INDEX(COLLECT({Project Training Request Form - Impacts}, {Project Training Request Form - Business Area}, [Impacted Areas]@row, {Project Training Request Form - Project Name}, [Project Name]@row), 1), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome - I was so excited when you answered my question because I knew I would get a great answer! Thank you! This was the issue!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!