Copy formula down, skipping cells but have it pull data in single increments from other sheet

As it seems that there is a constant pattern with the Sheet1 cells you need to reference, it is just a matter of constructing a formula to produce the correct Sheet1 row number based on the row number of Sheet2. It is rather easy to produce the referencing formula using some of functions ROW(), ISEVEN() or ISODD(), MOD() and FLOOR().

row number of Sheet1 = 31*FLOOR((ROW())/2;1)+42*FLOOR((ROW()+1)/2;1)

From the row number to actual formula with INDEX() function:

=INDEX(Sheet1!A:A;30*FLOOR(ROW()/2;1)+41*FLOOR((ROW()-1)/2;1)+42;1)

(in the formula, you obviously need to modify the range of the first parameter to fit your source data, and the number in the last parameter which states the number of the column in the range).

As the row number of Sheet2 is a main factor in getting the correct row in Sheet1, the above works only when the source data in Sheet1 starts at row 42 and the result data in Sheet2 starts at row 1. If that changes, you could of course subtract the row offset in the formula for each call to ROW() (for offset in Sheet2) and/or add/subtract to the resulting row number (for offset change in Sheet1).

Here's an example of how you could do it with formulas:

I'm using column A to illustrate the pattern that the meat of the formula produces. This starts in row 2 since row 1 would probably contain headings. This would be on sheet 2 where you want to build a list of items selected from sheet 1. The formula in A2 that calculates the target row numbers is:

=INT(ROW()/2)*42+INT((ROW()-1)/2)*31

The next screenshot illustrates how you would use that to actually pull a cell from the other sheet:

I stuck something to copy only in cell A42, which is why only one cell is filled in column B. The formula uses the INDIRECT function to build a cell reference. The formula in B2 is:

=INDIRECT("sheet1!A" & INT(ROW()/2)*42+INT((ROW()-1)/2)*31)

Cell A42 of sheet 1 contains ABC.