next row formula

E

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
 
M

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
 
L

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

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))
 
E

emm

Thank you, it worked very well!

Lars-Ã…ke Aspelin said:
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
 
E

emm

Thanks for your reply, it worked!

T. Valko said:
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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top