Cross Reference Formula Question
Hi Smartsheet Community! My organization utilizes a form to input ongoing audit data as shown in the first picture attached. Entered on the form are the employee no, incident date and a few other data points. This creates a rolling database for audits done throughout the months. For each row, based on the information entered on the form, formulas are used that calculate a "score" for each of 5 elements of the audit as well as a final audit score.
An audit score contributes to an employee's monthly performance score. I'd like to reference the audit element scores and the final audit score % of the audit rolling database onto the monthly performance score sheet (shown as the second image) to align with the employee no, and restricted to a specific incident date range.
I've tried Index/match and Index/collect and even played around with hierarchy relationships to no avail. Would appreciate some assistance :)
Help Article Resources
Check out the Formula Handbook template!