next row formula
 

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

next row formula
 

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

"emm" wrote:

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

next row formula
 

On Wed, 7 Jan 2009 12:47:02 -0800, emm
wrote:

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

next row formula
 

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

--
Biff
Microsoft Excel MVP


"emm" wrote in message
...
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

next row formula
 

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

"Mike H" wrote:

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

"emm" wrote:

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

next row formula
 

Thank you, it worked very well!

"Lars-Ã…ke Aspelin" wrote:

On Wed, 7 Jan 2009 12:47:02 -0800, emm
wrote:

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

next row formula
 

Thanks for your reply, it worked!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"emm" wrote in message
...
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

next row formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"emm" wrote in message
...
Thanks for your reply, it worked!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"emm" wrote in message
...
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