Getting fill handle to recognise a gap in the rows

[OwenMcH]

Hi,

A simple question, I'm hoping.

I'm creating a Sales reporting spreadsheet and have a main input sheet for the raw data. I would like to find an efficient way to copy data into the rows below (using the fill handle) where the cell being referred to in the next row references a cell 8 rows below the previously referenced cell.

When, on Sheet1, I simply use

Please Login or Register  to view this content.

in the first cell, and

Please Login or Register  to view this content.

in the second cell below it in the same column, and then highlight them both together and use the fill handle to copy down, the 3rd cell populates with Sheet2!C7, instead of Sheet2!C21.

How can I get Excel to continue the sequence from C21 instead of C7?

If I haven't explained clearly enough, I'm happy to provide an example workbook.

Many thanks,

Owen

[sktneer]

Hi,
Welcome to the Forum.
You may try this.....

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

[OwenMcH]

Hi Sktneer,

Thanks a lot for the quick reply.

I think you're on the right track, but it hasn't yielded the required results. Please see the attached sheet which is fairly self explanatory.

Many thanks for your help.

Owen

[sktneer]

Try this in B4

Please Login or Register  to view this content.

and copy down.

[martindwilson]

=INDEX(Sheet2!C:C,ROWS($1:1)*8-3)

[OwenMcH]

Thanks sktneer and martindwilson!!

Both solutions have done the job perfectly.

Many thanks! You've just saved me a lot of time.

Regards,

Owen

[sktneer]

You're welcome. Thanks for the feedback.

[OwenMcH]

Hi,

I'm not sure if it's kosher to continue with the thread once I've marked it as resolved, however as a continuation to my original request, I'm wondering if it's possible to highlight a table containing INDIRECT function formulae, and use the fill handle again to copy the table down to create each month for a year.

I have attached an example of how I've done it thus far.

Any help would be appreciated.