a way to copy/paste formula every 7th column that increments formula reference by 1

Your later example does not tie in with what you said earlier, as it is increasing the row number by 1 for a fixed column (S), whereas earlier you said you want to increment the column and keep the row number fixed. Which is it to be?

Pete

Sorry, you are correct...I messed that example up big time. It is now corrected. Everything is columns. Source is column S, T, U etc row 75.

Originally Posted by benishiryo

hi merlyn45, try:
=INDIRECT("'Press 1'!S"&75+FLOOR((COLUMNS($I$8:I8)-1)/7,1))

This didnt work except in the first column, I. When I pasted to column P and W, the result was a 0.

Try this in I8:

=index('Press 1'$S75:$BR75,int((columns($i:i)-1)/7)+1)

Then copy into P8, W8 etc.

Hope this helps.

Pete

Thanks so much Pete! Worked perfectly. I dont understand how it works...but it does!

Well, you basically have a formula that says =INDEX(range,number), where number is the element in the range where we want to get data from. As the range is S75 to BR75, we want number to take the values of 1, 2, 3, 4 etc when the formula is copied into cells I8, P8, W8 etc (i.e. every 7th column). The value of number is calculated in this expression:

int((columns($i:i)-1)/7)+1

when the formula is in column I. The COLUMNS part of this will return 1, from which we subtract 1 (leaving zero) and then divide by 7 (also leaving zero), and then take the integer part of that (also zero), and then add 1 - so this returns 1 when the formula is in column I.

When it is copied into column P, however, the expression becomes int((columns($i:p)-1)/7)+1, so the COLUMNS part returns 8, we subtract 1 (leaving 7), then divide this by 7 (leaving 1), then take the integer part of this (also 1) and then add 1 onto it, which will give us 2 when in column P.

Similarly, when it is copied into column W we get int((columns($i:w)-1)/7)+1, so the COLUMNS part returns 15, take 1 from this to get 14, divide by 7 to get 2, (same integer value) then add one to give us the result of 3.

So hopefully you can see how by copying this across into every 7th column, the value goes up by only 1 each time, and so we get successive elements of the original range returned.

Hope this helps.

Pete