Changing sheet reference when copying formulas

Vaibhav Khasgiwala

Hi there,

I am designing a process for employee performance reviews. The pre-built template set doesn't exactly do the things that I need done, so am having to build my own.

I have created a master sheet with a form to intake the performance data across various categories for different employees. We have 50 employees, and the data on the master sheet is unsorted. I have created workflows to automatically copy all submitted entries for Employee A to copy to a separate sheet ("Employee A Performance Review Sheet") that aggregates all Employee A data. Same for Emp B, C, etc for all 50 employees. So I have 1 master unsorted sheet with all data, and 50 separate sheets, 1 for each employee containing only that employee's data in the workspace.

I have created another sheet titled "Averages" which takes the average of all scores across each rated category for each individual employee (see below). I have done this for employee B in the screenshot by using the AVG formula and referencing the individual category column from the individual "Employee Performance Review Sheet" to calculate their average score based on all responses received.

Rather than going through this process 400 times (8 categories, 50 employees), is there a way to copy the formula. Or create a formula that somehow automatically references the employee's data sheet based on some criteria (email?).

Performance Averages.jpg

Formula I'm using for each category column:

=AVG({Employee B Flexibility})

=AVG({Employee B Self-Initiative})

etc

My goal is to then create a report with all the employee averages to put on a dashboard.

Thanks in advance.

Vaibhav

Alex Cruylles

Hi Vaibha,

As per my knowledge, I'm afraid that you'll have to create the link 400 times.

But before doing that I would recommend changing the process a little bit.

• Do you need the 50 sheets for any other reason than so split the results of each employee?

What I would have done: create the intake sheet unsorted like you did and then create the "Average" sheet where you applied the formula but to the intake sheet, not to each employee sheet.

There, you would reference one sheet but changing the lookup value based on a list of employees emails that you can type.

So you would use the formula "Collect" to group all results from employee A and then AVG to find the average for each category.

Therefor you would end up with a table of the average result for each employee for each category and can represent that in a Dashboard.

Hopefully I explained myself enough.

Regards

Vaibhav Khasgiwala

Hi Alex,

Thanks for the feedback. So it was actually suggested to me by a SS support staff to design the process with a master intake and set up automation to copy rows to employees' individual sheets to be able to then better run metrics/reports off the individual sheets specific to each employee. Ideally when I run reports I would be able to also filter by year in the report to get averages based on year to show trends/improvement, etc.

I tried to use the collect function but get an unparseable error:

=AVG(COLLECT({Employee Review Master Intake Flexibility}, {Employee Review Master Intake Sheet Range 1}, Employee Name))

Where the range is the sheet reference for column labeled "Flexibility", and criteria1 is defined as the sheet reference for column with the employee names.

But even if I did this approach, it seems to me that I'd have to change the employee name (criterion) and the reference range (category column) for each variable -- again 400x!

Am i missing something, or am I just in for a bunch of late nights creating manual formulas for each category for each employee? :(

Thanks!

Vaibhav

SoS | Dan Palenchar

You could simply create reports for each employee off the master sheet, rather than individual sheets. If the only purpose is to show each employee their info, you can create a single current user report and set the WHO section equal to the employee, and any employee can look at that one report and see their info.

In your average sheet, simply reference the master sheet so long as the employee name equals the name you are searching for. You can build a report off this to display on a dashboard.

It should not be particularly cumbersome to solve this in a scalable way.