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), "")
-
Happy to help. 👍️
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), "")
-
@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. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!