Automating an Equation

[John Davis]

Good afternoon all,
I have an equation that takes a row of 18 cells and puts it into a column of 18 cells. No problem there. But what I need to know is how to use the equation for the next set of 18 cells right below the first. This is my equation =INDEX($B$1:$S$1,ROWS(A$1:A1)). I put this in A1 and auto fill drag down to row 18.

How do i repeat this step for my next 18 rows (that have different information that the top row)? A small example below. So after I auto fill here from A1 to A3, how can I alter the original equation to keep going and make r s and t appear in A4:6? Is there a way to +18 to all the numerals in the formula so it will keep adding 18 (in the example +3) for every next set? But still be able to auto fill (A2, A3 in the example)?

A B C D
1 x x y z
2 y
3 z
4 ? r s t
5 ?
6 ?

Thanks!

PS I attached the top portion of the file in question to this thread.

[Ken Puls]

Hi John, and welcome to the forum,

It's late here, and I'm a little tired, but if you're just trying to copy the block of formulas down, you could do a Find/Replace on your existing selection to replace $1 with 1. At that point if you copy the block from Rows 1:18 down to 19:36, it would apply the correct formulas.

I'll try to look at this again to solve your original question late this weekend.

[Bob Phillips]

Try this

=INDEX($B$1:$S1,(INT((ROW()-1)/18)*18)+1,IF(MOD(ROW(),18)=0,18,MOD(ROW(),18)))

[John Davis]

This worked great, thanks for the help. You guys seriously saved me over 100 hours of work for this project I'm working on. Much appreciated.