| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
