fayetality
New Member
- Joined
- Jun 27, 2014
- Messages
- 3
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:
Thank you for taking the time to read this.
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.
