#### fayetality

##### New Member

- Joined
- Jun 27, 2014

- Messages
- 3

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

=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.