Assign formula every 16th row on sheet "Report" to reference other sheet

I am trying to reference cells in Column A of Sheet "Job Summary” from Sheet “Report”.

Except I need to allow 15 empty rows between each referencing formula on “Report” (16 rows total including the row with the formula).

So, dragging down the formula does not work. I thought I could create a filter to hide the 15 rows in the middle, then copy down, but that copies the formula to the hidden rows as well.

The 500 cells to be referenced are in order on “Job Summary” in a column A3:A502..

Need to copy this down 8,000 rows on “Report” so really need a solution.

The first formula should be entered in cell A4 on “Report” referencing Cell A3 on “Job Summary”.

Cell A4 on “Report” ='Job Summary'!A3 Cell A20 on “Report” =’Job Summary!A4 Cell A36 on “Report” =’Job Summary!A5

And so on…

Need a solution to copy down this formula on “Report”.

• Does this answer your question? Increment Cell Reference in Formula by 10 Dec 21, 2020 at 23:22
• Not exactly. I have seen that. I need the formula to be every 16 rows, but referencing only one additional row at the source. Dec 21, 2020 at 23:56
• That solution allows the referencing formula to reference every x number of rows. Kinda exactly backwards from what I need. I have seen some solutions using mod()and some math -12)+2... but don’t understand them enough to recreate for my 16 row spacing on the cells containing the formula. Dec 21, 2020 at 23:59
• Yes, I agree; I retracted that duplicate reference but obviously it hasn't gone. BUT... I did work out a solution; refer below. Dec 22, 2020 at 0:39

You want to enter a formula in sheet="Report" for the respective value in "Job Summary". The values in "Job Summary" are on sequential rows (A3:502). The first formula on sheet="Report" should be in Cell A4, and then every 16 rows (leaving 15 blank rows between each formula). For example, Cell A4, A20, A36, etc.

There are probably many solutions to this question. This is a quick-and-dirty answer that can be done accurately and reliably in a minute or two. No doubt there are other, more elegant answers (such as that provided by Dimension Datacraft) that would also be solutions to your question.

1 - Report:Cell C1 = `3`

• For the sake of transparency, I created a variable for the starting row on "Job Summary". You could just as easily build this into the formula.

2 - Report: Cell A4

• `='Job Summary'!A3`
• manual entry of the first value

3 - Report: Cell A20

• `=indirect(address((counta (\$A\$4:A4)+\$C\$1),1,4,true,"Job Summary"))`

4 - Select and copy the range Report:Cell A20:A35

5 - Select the range Report: A36:A8003

6 - Paste the clipboard value to the selected range.

• This will create a further 498 copies of the formula, each separated by 15 blank rows, and each referencing the preceding cell on sheet="Job Summary".
• Thank you for this response! I will also try this first thing in the morning. Dec 22, 2020 at 0:46

Here is another option to consider:

`=ArrayFormula(FLATTEN(SPLIT(FILTER('Job Summary'!A3:A,'Job Summary'!A3:A<>"")&REPT(",",15),",",0,0)))`

How It Works

`FILTER` filters in only non-blank entries from 'job Summary'!A3:3.

To each filtered row is concatenated a repetition of 15 commas, which are then `SPLIT` at the commas keeping empty text (as signified by `0` in the last parameter of `SPLIT`). This forms a virtual grid of each non-blank target string with 15 nulls to its right.

That grid is then `FLATTEN`ed into a single column.

Note: `FLATTEN` is still an undocumented Google Sheets function.

• IMHO, this is the best answer Dec 22, 2020 at 22:15
• Thanks, @Tedinoz. I honestly don't care if my answers are chosen as "Best Answer" in the sense of points or checkmarks. I'm more about efficiency, so I just want someone's answer checked to mark an issue as resolved. These forums prove that there is always more than one way to approach a problem. I just figured this approach might be easier for "newbies" to adjust to their own needs. Dec 22, 2020 at 23:45
• Thanks for the help today with this. I got it all working well now. Cheers! Dec 23, 2020 at 2:11
• Happy to hear it, @Cohutta68 Dec 23, 2020 at 2:34

Try this formula in cell A4 on your Report Sheet

``````=sort('Job Summary'!A3:A,sort(row('Job Summary'!A3:A),mod(Row('Job Summary'!A3:A)-4,16),0),1)
``````

This will just take the range 'Job Summary'!A3:A and separate each row by 15 blank rows. Basically, show the value skip 15 rows show the next value.

You could just put the formula in and leave it, but know that technically this formula is outputting into every cell from 'Report'!A4 down. Meaning if you were to type in a blank cell between two of the values the formula would throw an error (because there is something blocking the formula from expanding it's output fully).

If you don't want to leave the formula and / or need to take action on those blank cells between the values... then simply copy this range and paste as values over itself to make it static.

• I copied and pasted the formula: I got "1" in cell A4 (which is right), then blank from A5:A11, then A12=498, A13=436, A14=374, and so on reducing by 62 until A20=2. BTW, the questioner wants 15 blank rows between each formula. Dec 22, 2020 at 0:36
• Excellent. Thank you! I will check this first thing in the morning. It sounds like you are exactly right on with the issue. Dec 22, 2020 at 0:45
• That is correct, needs to be 15 blank rows between each formula. Which means there is a new formula on every 16th row. Dec 22, 2020 at 0:46
• Here is a sample sheet to help and avoid any confusion (docs.google.com/spreadsheets/d/…). @Tedinoz. I'm not seeing that happening on my sheet. And yes sorry for the confusion.. this formula puts the next value 16 cells away (15 blank) I will clarify that in my answer. Thanks for pointing that out. Dec 22, 2020 at 0:53
• Also no worries on leaving the space blank between each formula. Basically leading the entire column blank to allow the formula to function properly. There are no other requirements other than the formula in that column. Dec 22, 2020 at 0:58