Creating an audit scorecard from a submitted form

Hello,

I'm having a hard time creating a reusable audit scorecard from a form. Example: a person performing the audit would fill out the form questions. Each question has a choice of three dropdown responses of (Pass, Fail, Didn't evaluate). After submitting the form the user is redirected to a second smartsheet URL (the scorecard) where its determined if they passed or failed the audit.

I'm struggling with what formula to use and the fact that each time a new form submission is entered it either moves to the top or the bottom of my forms master sheet. Because I'm using dropdown (single select) I've tried referencing my data from the master sheet on the scorecard using COUNTIFS. I haven't had any luck trying to statically pull the data from a referenced sheet using COUNTIFS.

Any advice or suggestions on how to accomplish this or get me pointed in the right direction would be very helpful.

Thanks

Answers