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?


Tags:

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!