Excel: Copy every nth line from one sheet to another

Question

In Excel, I'm trying to copy data from one sheet into another. Each data point is separated by nine rows. On the new sheet, I begin from the sixth row and I continue this for 20 rows. For example:

B6=Scenarios!H7
B7=Scenarios!H16
B8=Scenarios!H25
...

I wouldn't mind doing this manually, but I have a lot of columns to fill, based on this pattern.

C6=Scenarios!H8
C7=Scenarios!H17
C8=Scenarios!H26
...

I manually fill a couple columns and skip a couple 'Scenario' sheet rows before continuing this pattern.

F6=Scenarios!H11
F7=Scenarios!H20
F8=Scenarios!H29
...

E6=Scenarios!H12
E7=Scenarios!H21
...

I have looked around and I know that the INDIRECT or OFFSET command can help, like

F5=INDIRECT("Scenarios!H"&((ROW(H11)*9)))

or

F5=INDIRECT("Scenarios!H"&((ROW($H7)*1)+4))

Yet I'm unable to get a formula going that works correctly in a manner where I can simply copy down cells and have it fill correctly. Can anyone help me on the math for this, or otherwise provide a better solution?

Quick clarification - Do you want to literally be skipping every Nth line, or would a formula work? I have a formula that you can use that, given a range (say A1:A10), can return only those cells that have something in them. See this thread for an example on what I mean. Would something like that work? — BruceWayne
Well, it's like this: Sheet1) Variable A | 1234 Variable B | 9876 Sheet 2) Variable A | Variable B — TheHolyDarkness
Well, it's basically a normalization issue. I have various concatenation macros automatically building JSONs and various other scripts based on what I feed it. I just needed a macro or formula that'd enable to only have to change data in one place in order for it to be reflected elsewhere. — TheHolyDarkness

SincereApathy SincereApathy · Accepted Answer · 2016-02-04T00:33:18

B6 = =INDEX(Scenarios!H:H, ROWS($B$6:B6)*9-2)

C6 = =INDEX(Scenarios!H:H, ROWS($B$6:B6)*9-1)

F6 = =INDEX(Scenarios!H:H, ROWS($B$6:B6)*9+2)

E6= =INDEX(Scenarios!H:H, ROWS($B$6:B6)*9+3)

Copy formulas down and you get the idea of math if you have other columns to fill.

Excel: Copy every nth line from one sheet to another

1 Answers