Fill Help

[Justin]

How do i do the following i want to do a fill where it fills every 5th cell

example

A1 =1
A2 = 2
A3= 3
A4=4
A5 = 5
etc

I want to then do B1 = A1, B2 = A5, B3 = A10, etc. How do i do this with out
doing it manually?

Thanks,

 

[Tom Hutchins]

In B1: =A1
In B2: =INDIRECT(ADDRESS((ROW()-1)*5,1))

Copy B2 down as needed.

Hope this helps,

Hutch

 

[Justin]

Ok that works except that it always gives column A, how do i have it give me
column D, or column C in another sheet?

 

[Tom Hutchins]

The last number in the B2 formula (after the comma) is the number of the
column being referred to. So, to apply the formulas to column D instead of A:

In B1: =D1
In B2: =INDIRECT(ADDRESS((ROW()-1)*5,4))

To refer to column C on another sheet (for example, Sheet2):

In B1: =Sheet2!C1
In B2: =INDIRECT("Sheet2!" & ADDRESS((ROW()-1)*5,3))

Hope this helps,

Hutch