INDEX/COLLECT FORMULA PUSHING OUT "#INVALID VALUE" - CAUSING CHECKBOX FORMULA TO FILL IN WITH SAME

Meagan Harte

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?

Paul Newcome

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

Happy to help. 👍️

Paul Newcome

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), "")

Meagan Harte

@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!!!

Paul Newcome

Happy to help. 👍️