use two different ranges in a formula

**Cloutier** · 04-01-2010, 04:35 PM

Sorry about the title, I didn't know to say it in less than 35 words!

What I'm trying to do is make a new table from data on other sheets. On the first data table (sheet 1), I have averages on cells B17:M17. On the second one (sheet 2), I have numbers on cells L4:L87.

On the new table, I want in B2: sheet1!B17/sheet2from L4, 7th cell down column L. In B3, sheet1!C17/sheet2!from L4, 14th cell down column L, and so on.

The problem I have when I type the formula in cell B2 and copy it down is that Excel doesn't know I want to move horizontally on the first sheet, and every 7th cell vertically on the second sheet.

Is there a formula that will allow me to do this?

Thanks for your help.

**JBeaucaire** · 04-02-2010, 10:27 AM

Can you post a workbook showing the first two formulas if you were to do it by hand?

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**DonkeyOte** · 04-02-2010, 10:42 AM

Perhaps

B2: =INDEX(Sheet1!$B$17:$M$17,ROWS(B$2:B2))/INDEX(Sheet2!$L$4:$L$87,7*ROWS(B$2:B2))
copied down

**Cloutier** · 04-02-2010, 04:50 PM

Thanks DonkeyOte! It works.

Would you mind explaining to me how this formula works?

Thanks again.

**DonkeyOte** · 04-03-2010, 12:36 PM

The first value in your calculation will stem from range Sheet1!B17:M17

The first formula ie B2 will reference Sheet1!B17, B3 will use Sheet1!C17 and so on and so forth.

We use INDEX to reference the vector housing the source values and use an incremental row count to determine the appropriate value to be returned from said vector

=INDEX(Sheet1!$B$17:$M$17,ROWS(A$2:A2))

in the first calculation the row count [A$2:A2] will be 1 thus first value is returned from the vector (Sheet1!B17)

in the second calculation (B3) the row count will be 2 [two rows in range A$2:A3] thus second value is returned from the vector (Sheet1!C17)

The second value in your calculation will stem from range Sheet2!L4:L87 only here the first value used will be L10 - the 7th value, second will be L17 - the 14th value etc..

As we can see the underlying premise is the same as before only now the step between values is different (7 rather than 1).
In terms of automating this we use the exact same principles as per the first calculation (ie INDEX & row count) except the incrementing row count is multipled by 7
(this means that the first value returned is the 7th value in the vector (1*7), the second value is the 14th value in the vector (2*7) and so on and so forth)