Formula in every 5th cell to reference ever 1st cell on another sheet.

[vllgv]

Given:

Formula in cell A1: =Data!A1
Formula in cell A6: =Data!A2

I need cell A11 to reference A3 on the data sheet and so on.

Is this possible?

Luke

 

[Rick Rothstein]

Given:

Formula in cell A1: =Data!A1
Formula in cell A6: =Data!A2

I need cell A11 to reference A3 on the data sheet and so on.

Is this possible?

What should be in cells A2,A3,...,A7,A8,... etc.?

 

[Useful]

Given:

Formula in cell A1: =Data!A1
Formula in cell A6: =Data!A2

I need cell A11 to reference A3 on the data sheet and so on.

Is this possible?

Luke

Hello!
like that?
this code inserts formula.

Sub Step5()i = 1
r = 1
Do
Cells(r, 1).Formula = "=Data!A" & i
r = r + 5
i = i + 1
Loop Until Sheets("Data").Cells(i, 1) = ""
End Sub

 

[markmzz]

Maybe this:

In A1

=IF(MOD(ROWS($A$1:$A1)-1,5),"",INDIRECT("Data!A"&CEILING(ROWS($A$1:$A1),5)/5))

And copy to A6, A11, ...

Markmzz

 

[vllgv]

Thank you this worked

 

[markmzz]

Thank you this worked

You're welcome.

And you can try this too:

=IF(MOD(ROWS($A$1:$A1)-1,5),"",INDEX(Data!$A:$A,CEILING(ROWS($A$1:$A1),5)/5))

Markmzz