Copying a formula but changing a row reference.

**Glayva** · 03-08-2013, 08:38 AM

I have inherited a workbook that I am trying to create an overview sheet for.

As you can see from the sample sheet attached, I need to pull metrics from the data sheet into the overview sheet to show a month by month breakdown. However, there are 60 rows between the metric reported for January and the same metric reported for February.

I know I can go through the sheet manually and marry up each metric, but this will take an eage, given the size of the actualy workbook.

So I am looking for a formula which will basically copy the metric formulas for January (row 5) but move the row references on by 60 rows. I'd then like to repeat that for March (another 60 rows on), etc, etc, etc.

Can this be done?

Thanks

G

**Cheeky Charlie** · 03-08-2013, 09:20 AM

Is the extra two lines at row 60 of sheet 2 an error? The solution is simpler if the tables on sheet 2 are a consistent difference apart.

**Cheeky Charlie** · 03-08-2013, 09:21 AM

Is the extra row at row 60 of sheet 2 an error? The solution is simpler if the tables on sheet 2 are a consistent distance apart.

What about the months? Are there always two tables between months?

**FDibbins** · 03-08-2013, 09:48 AM

Hi there

I would suggest a slight change to the tables on sheet 2. In the attached suggestion, I have (started to ) created a table that will groww sideways as the months progress. That makes it easier to use index/match to return the values you want.

Take a look and let me know if that is something you can work with?

**Glayva** · 03-08-2013, 10:14 AM

Thanks FDibbins, but I can't make any changes to sheet 2 - life would be so much easier if I could! (there are actually several tables horizontally across the sheet)

**Glayva** · 03-08-2013, 11:02 AM

Cheeky Charlie - I was confused as to why you were asking to begin with but then I realised that I had labeled the metrics in the two different sales the same.

I have uploaded a better example which indicates that metric 1, for example, appears in row 3 and 63 (123 for March, 183 for April etc) so yes, there is a consistent difference between the associated metrics.

Does that help?

**Cheeky Charlie** · 03-08-2013, 11:36 AM

Yes. Constant difference means a simpler formula. Good news for you.
Your example is slightly misleading in that you've labelled B:C on overview 'metric 27' but it points at metric 126 in Data.
Rather than get hung up on it, here is a simple and adaptable formula that addresses your needs:
=B5
INDEX(Data!C:C,ROW(1:1)*60-2)
Copy across one and down to the bottom.

This works by addressing the entirety of column C and pulling the value at address (row*60)-2. This equates to 58 for row 1, and 118 for row 2. To make an equivalent you need to tinker with the offset (-2) to make it point at the relevant row.