Cross Reference Formula Question

priderock

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 :)

ericncarr

Is it possible to have multiple inputs for an employee within a month? If so, do you want to get the average or...?

If not, it's simpler, but if you need to average or do something else the formula will need tweaking.

First, for the monthly sheet. I would create a summary field if you have access to that, or 2 columns if not that will calculate the first and last day of the current month so you have a date range that rolls over each month automatically.

First Day of Current Month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

This gets the current year as of today's date, the current month as of today's date, and sets it to day 1 (so 9/1/22 right now).

Last Day of Current Month:

=IF(MONTH(TODAY()) <> 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY()), MONTH(TODAY()), 31))

If it isn't December, this will get get the first day of the next month and subtract that by 1, giving you the last day in the current month. If it is December, it will set the last day as 12/31/20XX (XX depending on current year).

Now you have your date range to compare your lookups to. I'm writing the formula for using a sheet summary

=index(collect({Ref to Final Audit Score % column}, {Ref to Employee No Column}, = [Employee No.]@row, {Ref to Incident Date column}, >=[Month Start Date]#, {Ref to Incident Date column}, <=[Month End Date]#), 1)

You would need similar formulas for any other columns you're collecting, changing the first reference to be a reference to the desired column value. It then compares the Employee No. to the current row's Employee No. and compares the date to be equal to or greater than the current month start and less than or equal to the current month end.

If you want to average you could do an avg(collect()) instead of index(collect()).