Copying formulas that only grab particular ranges

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:
  • 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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

VorLag

Board Regular
Joined
May 13, 2013
Messages
205
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

New Member
Joined
Jun 27, 2014
Messages
3
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 #NameSupervisorEmployee IDReference #InfractionTest CountCategory 1, Question #1Category 1, Question #2Category 2, Question #1
12Employee NameSupervisor Name12345671CCV2GGA20%100%0%
1CCV2GGB100%0%100%
15Employee2 NameSupervisor Name12344561CVC3BBU1100%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 #NameSupervisorEmployee IDTest CountOverall ScoreOverall 1st HalfCategory 1 Category 2
12Employee NameSupervisor Name1234567233%33%50%0%
15Employee2 NameSupervisor Name12344561100%100%100%100%
15Employee3 NameSupervisor2 Name1234678
15Employee4 NameSupervisor2 Name1234882

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

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top