Offset using fill-handle

[bilks]

Hi guys

I have two worksheets. W1 has data and I would like column A in W2 to reference cells on a particular row in W1, but spaced 8 cells apart. So, I have the following in W2:

Cell A1=OFFSET(Sheet1!$G$2;0;0)
Cell A2=OFFSET(Sheet1!$G$2;0;8)
Cell A3=OFFSET(Sheet1!$G$2;0;16)

I would like to copy this function down column A, increasing the final number if the formula by 8 each time. However, if I use the fill-handle, Cell A4 becomes the same as A1, A5 copies A2, A6 copies A3 and A7 copies A1 again.

Is there any way I can do this?

Thanks.

 

[JoeMo]

Hi guys

I have two worksheets. W1 has data and I would like column A in W2 to reference cells on a particular row in W1, but spaced 8 cells apart. So, I have the following in W2:

Cell A1=OFFSET(Sheet1!$G$2;0;0)
Cell A2=OFFSET(Sheet1!$G$2;0;8)
Cell A3=OFFSET(Sheet1!$G$2;0;16)

I would like to copy this function down column A, increasing the final number if the formula by 8 each time. However, if I use the fill-handle, Cell A4 becomes the same as A1, A5 copies A2, A6 copies A3 and A7 copies A1 again.

Is there any way I can do this?

Thanks.

Select a cell and name it myOffset. In that cell enter an 8 (or any other integer value you want to use as an offset). Then use this formula in cell A1 and fill down.

=OFFSET(Sheet1!$G$2,0,myOffset*ROW(A1))

 

[AlphaFrog]

Try something like this...

=OFFSET(Sheet1!$G$2;;(ROW(A1)-1)*8)

 

[bilks]

You are a legend. If I could buy you a drink, I would.

I tweaked it a little to suit my spreadsheet exactly, but it worked a treat. Champion.