Spreading a list.

[Guest]

This is best described by example:
I need Sheet 2, Cell A1 to reference Sheet 1, Cell A1. Sheet 2, Cell A35 to
reference Sheet 1, Cell A2. Sheet 2, Cell A70 to reference Sheet 1, Cell A3.
Etc.

How do I do this!?

 

[R.VENKATARAMAN]

you have not given how many values are there in sheet1 and column A. I
assumed only 10 values.
if different change the line
i=1 to 10
then use this sub

Public Sub taest()
Dim i As Integer
Dim j As Integer
For i = 1 To 10
Worksheets("sheet2").Cells(35 * i, 1).Value = Worksheets("sheet1").Cells(i +
1, 1).Value
Next i
Worksheets("sheet2").Range("a1") = Worksheets("sheet1").Range("a1")
End Sub
===================================

 

[Ken Wright]

In cell A35 on sheet 1 put

=INDIRECT("Sheet2!A"&ROW()/35+1)

select A1:A35 and then grab the bottom corner of the range that looks like a
little black cross and drag down as far as needed. Now go back and in cell
A1 just link it directly to cell A1 on the other sheet.

NOTE:- If you are likely to insert or delete any rows on this sheet then I
would not suggest this option.

 

[Ken Wright]

If you have NOTHING else in Col A in sheet1 other than these formulas, then
follow the instructions I gave you but use

=OFFSET(Sheet2!$A$1,COUNTA($A$1:A34),0) in cell A35

and then follow the rest as stated. You'll be 1 row off until you complete
cell A1.