Copying formulas that only grab particular ranges

[fayetality]

I found some previously solved posts that are similar to what I need, but I am having trouble making their solutions work for myself. I would really appreciate anyone's help! Here's the scenario:

I have created a report that holds the grades of certain calls that have a corresponding reference number. The 'Scores' sheet's first row consists of the employee information, the reference number, and then the corresponding scores by the reference number. However, there are 5 more rows following the first one assigned to an employee, and then the 7th is a new employee. The rows under the employees information (within the rows that belong to them) are blank until the column in which a new reference number can be entered. This is to accommodate multiple grades.

In another sheet called 'Stats', that will pull from 'Scores', the employees are listed one after the other. I need the formula to pull the data from row 4 to row 9, and then for the next employee row 10-15, the next 16-21, and so on. It will need to do this for 22 columns that pull various parts of the data. For example, the first section will need to pull from L:M and AH:AH, the second N:O and AI:AL. I have the ranges in already, so my primary concern is:

• Being able to simply fill down the rows. I already took the time out to correct each individual cell, but now a new report is being requested for a different (and much larger) set of employees and I cannot take the time out to do this for 600+ people.
• The formula needs to average the ranges I already have set. Here's are examples of the formulas I am using to calculate percentages for the first row of pulled data:

=IF(ISERROR(AVERAGE(Scores!L4:BE9)), "", AVERAGE(Scores!L4:BE9))
=IF(ISERROR(AVERAGE(Scores!L4:M9,Scores!AH4:AH9)),"",(AVERAGE(Scores!L4:M9,Scores!AH4:AH9)))
​

So when I fill down, the next formula should say the exact same ranges, but the should have 10 & 15 in place of 4 & 9.​

Thank you for taking the time to read this.

 

[VorLag]

Can you show an example of what the data looks like? In a table, perhaps? I'm not sure I'm following what you mean here.

 

[fayetality]

Can you show an example of what the data looks like? In a table, perhaps? I'm not sure I'm following what you mean here.

'Scores' looks something like this. I tried to simplify it. Call Count is automatically calculated based on the number of reference numbers enter (the number of tests graded). Question #1, #2, #3, etc..This is where the scores per question will be entered. Either they got it or they didn't, so it will always be a 100% or a 0%:

Location #	Name	Supervisor	Employee ID	Reference #	Infraction	Test Count	Category 1, Question #1	Category 1, Question #2	Category 2, Question #1
12	Employee Name	Supervisor Name	1234567	1CCV2GGA		2	0%	100%	0%
				1CCV2GGB			100%	0%	100%
15	Employee2 Name	Supervisor Name	1234456	1CVC3BBU		1	100%	100%	100%

<tbody>
</tbody>

The 'Stats' sheet that pulls from scores is like this. It grabs two sets of data, from the first half of the test and the second half. I excluded the 2nd half because it's a bit confusing, and the same formula is needed, just a different set of ranges.:

Location #	Name	Supervisor	Employee ID	Test Count	Overall Score	Overall 1st Half	Category 1	Category 2
12	Employee Name	Supervisor Name	1234567	2	33%	33%	50%	0%
15	Employee2 Name	Supervisor Name	1234456	1	100%	100%	100%	100%
15	Employee3 Name	Supervisor2 Name	1234678
15	Employee4 Name	Supervisor2 Name	1234882

<tbody>
</tbody>

So Overall Score pulls the entire data set from 'Scores' per employee (In my case the first employee's row would be pulling L4:BE9, the next would pull L10:BE15 [the idea is to add 6]. Also, Overall 1st Half pulls the range L4:AG9, Category 1 pulls L4:M9, Category 2 pulls N4:O9.)

I hope this helps! Let me know if you need more from me.

 

[fayetality]

Can anyone help, please?