next row formula

[emm]

I need a formula on one sheet to reflect a number in another sheet. That
number must be repeated 3 times, then the formula needs to go to the next row
and return the new number.
Sheet 1: 12345
12352
Sheet 2: an example of the outcome I need...
12345
12345
12345
12352
12352
12352

 

[Mike H]

Hi,

Someone may come up with a formula solution but I can only do it with a macro.
Right click sheet1 sheet tab, view code and paste this in and run it.

Sub prime_lending()
x = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
For x = x To x + 2
Sheets("sheet2").Cells(x, 1) = c
Next
Next
End Sub

Mike

 

[Lars-Åke Aspelin]

I need a formula on one sheet to reflect a number in another sheet. That
number must be repeated 3 times, then the formula needs to go to the next row
and return the new number.
Sheet 1: 12345
12352
Sheet 2: an example of the outcome I need...
12345
12345
12345
12352
12352
12352

If the first number on Sheet1 is in cell A1, then you can try this
formula in the first row (in any column) on Sheet2

=OFFSET(Sheet1!A$1,INT((ROW()-1)/3),0)
Copy down as far as needed.

Change the "-1" if you want the result to start on some other row.

Hope this helps / Lars-Åke

 

[T. Valko]

Here's a non-volatile approach...

Assume your data on Sheet1 is in the range A2:A10

You want the results to appear on Sheet2 starting in cell A2.

Enter this formula on Sheet2 cell A2 and copy down as needed:

=INDEX(Sheet1!A$2:A$10,CEILING(ROWS(A$2:A2)/3,1))

 

[emm]

Thanks Mike, I got it to work for me. I appreciate it.

 

[emm]

Thank you, it worked very well!

If the first number on Sheet1 is in cell A1, then you can try this
formula in the first row (in any column) on Sheet2

=OFFSET(Sheet1!A$1,INT((ROW()-1)/3),0)
Copy down as far as needed.

Change the "-1" if you want the result to start on some other row.

Hope this helps / Lars-Ã…ke

 

[emm]

Thanks for your reply, it worked!

Here's a non-volatile approach...

Assume your data on Sheet1 is in the range A2:A10

You want the results to appear on Sheet2 starting in cell A2.

Enter this formula on Sheet2 cell A2 and copy down as needed:

=INDEX(Sheet1!A$2:A$10,CEILING(ROWS(A$2:A2)/3,1))

 

[T. Valko]

You're welcome. Thanks for the feedback!