reference autofill.

[mtnone]

I am pulling data for certain cells on another sheet. It is every 7 cell
that I need from one column of about 1000 rows. When I work on a
referencing the cells I am unable to properly autofill.

For example
I enter =i5 in a cell and =i12 below that that cell and then autofill
then next cell and get =i7, instead of =i19

How can I set this up to count by sevens so when when I fill I am able
to reference i5, i12, i19, ect without typing everything in by hand?

Thank you.

 

[MartinShort]

One way of doing it would be to set up a helper column and use the
OFFSET function.

Try this on a blank sheet as an example:

For the helper column in this case column A enter 5, =A1+7, =A2+7, =
A3+7... so the column will display 5, 12,19, 26 etc.

In my example, I used column B as a numerical column 1,2,3,4,...to
prove a point; the aim being to pick off every 7th id which we will
come to next using the OFFSET function.

In cell C1 enter "=OFFSET(A1,A1+1,1)" and then fill this down - and you
have just picked up every 7th reference. All you need to do now is
modify the formula to work across sheets and you're there.

Enjoy

 

[Guest]

Hi,

you can try a formula like this

On the fist line
=INDIRECT(address(row();9;;;"Plan1"))

For the next lines add 6 rows
=INDIRECT(address(row()+6;9;;;"Plan1"))

Assuming that "plan1" is the name of the spreadsheet and the is collum "I"
(the 9th)

hope it help and thanks for the feedback
regards from Brazil
Marcelo



"mtnone" escreveu:

 

[Guest]

try this mark first cell u want formula in
Sub Lasey()
Dim i
For i = 5 To 1000 Step 7
If Range("A" & i) <> "" Then ' Change A to urs kolumn
ActiveCell.Formula = "=A" & i ' Change A to urs kolumn
ActiveCell.Offset(1, 0).Activate
End If
Next
End Sub

 

[Guest]

Sub Lasey2()
Dim i
For i = 5 To 1000 Step 7
If Range("Sheet1!A" & i) <> "" Then
ActiveCell.Formula = "=Sheet1!A" & i
ActiveCell.Offset(1, 0).Activate
End If
Next
End Sub