Thread: Formula to link a column of data between 2 worksheets and duplicate rows

I'm in need of a formula, or another automated technique, to link a column of data from one worksheet to another. I have an input worksheet that I copy the raw data into that is formatted as follows:

COLUMN A
Row 1 12001
Row 2 12004
Row 3 12011
Row 4 12020
Row 5 13050
etc...

I need this import sheet to link to an output worksheet that would be updated automatically as new data is entered into the input sheet. However, I need the output sheet to copy the input values a specified # of the times and list them. For example, if the specified # was 3, it would be formatted as follows:

COLUMN A
Row 1 12001
Row 2 12001
Row 3 12001
Row 4 12004
Row 5 12004
Row 6 12004
Row 7 12011
etc...

I need the formula, or method, to be one that I don't have to continually re-enter as the data changes, as I have thousands of records to do this with. I've tried filling down, but autofill doesn't recognize an accurate trend. Is this something that I can accomplish by combining several fomulas, creating macros, or a combination of the both? Any help would be greatly appreciated.

If you can start in Row 2 of Sheet2 (i.e.in Sheet2!A2) then:

and assuming raw data starts in A1 of Sheet1....

Code:

=IF(COUNTA($A$1:A1)>=COUNTA(Sheet1!A:A)*3,"",INDEX(Sheet1!A:A,MOD(INT((ROW(A1)-ROW($A$1))/3),COUNTA(Sheet1!A:A))+1))

copied down

Replace the 3's with an absolute cell reference containing a 3 if you want it to be more dynamic.